SSAS Cube Calculations (YTD, BTD, PY, PYTD)

Actual YTD

AGGREGATE ( PERIODSTODATE( [Financial Date].[Calendar FY-M].[Financial Year],[Financial Date].[Calendar FY-M].CurrentMember ), [Measures].[Actual] )

Actual BTD

AGGREGATE ( PERIODSTODATE( [Financial Date].[Calendar FY-M].[(All)],[Financial Date].[Calendar FY-M].CurrentMember ), [Measures].[Actual] )

Actual PY
( ParallelPeriod ( [Financial Date].[Calendar FY-M].[Financial Year], 1, [Financial Date].[Calendar FY-M].CurrentMember ), [Measures].[Actual] )

Actual PYTD
AGGREGATE ( PERIODSTODATE( [Financial Date].[Calendar FY-M].[Financial Year]
    , ParallelPeriod ( [Financial Date].[Calendar FY-M].[Financial Year], 1, [Financial Date].[Calendar FY-M].CurrentMember ) )

CUBEVALUE() and CUBEMEMBER() and CUBESET(), oh my!

Excel Cube functions (reference)

Return a value from the cube.

=CUBEVALUE(connection,member_expression1,member_expression2…)

=CUBEVALUE("Cube Connection",$B$4,$B$5,$B$52,$B$50,$A40)

=CUBEVALUE("Sales","[Measures].[Profit]","[Time].[2004]","[All Product].[Beverages]")

Returns a member or tuple from the cube.

=CUBEMEMBER(connection, member_expression, [caption])

=CUBEMEMBER("Cube Connection","[Invoice Date].[Calendar FY-M].&[MAR FY2013])

=CUBEMEMBER("Cube Connection","[Invoice Date].[Calendar FY-M].&["&B6&"]")

Defines a calculated set of members or tuple. Can be used as a member expression by the CUBEVALUE() function.

=CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])

=CUBESET("Cube Connection", "FILTER( [Invoice Date].[Hierarchy].members, [Invoice Date].[Hierarchy].Currentmember.Properties('Key0', TYPED) > "&B47&" AND [Invoice Date].[Hierarchy].Currentmember.Properties('Key0', TYPED) < 65000)", "(Multiple Items)")

=CUBESET("Cube Connection","{[Invoice Date].[Hierarchy].&[2012],[Invoice Date].[Hierarchy].&[JAN FY2013],[Invoice Date].[Hierarchy].&[FEB FY2013],[Invoice Date].[Hierarchy].&[MAR FY2013]}","(Multiple Items)")

Return the Key value from selected member

[Invoice Date].[Hierarchy].Currentmember.Properties('Key0', TYPED)