SSAS How to calculate Variance, YTD, Prior Year in MDX

To use any of this code, go into your cube, click on Calculations Tab, then Script View, and just paste it in there. Or you can build it the long way through the form
 view.


SIMPLE VARIANCE
Example: Difference of Actual sales (A Value) from Budgeted Sales (B Value)
You run into problem with zero values on the Budget side if you're dividing by the budget to get your variance percentage.
--------------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[B Var Value PC]
 AS case

when [Measures].[B Value] = 0
and [Measures].[A Value] > 0
then 1

when [Measures].[B Value] = 0
and [Measures].[A Value] < 0
then -1

when [Measures].[B Value] = 0
and [Measures].[A Value] = 0
then 0

else [Measures].[B Var Value]/[Measures].[B Value] end,
FORMAT_STRING = "Percent",
NON_EMPTY_BEHAVIOR = { [A Value] },
VISIBLE = 1;
--------------------------------------------------------------------------------

YTD CALCULATIONS
YTD Calculations require that your time periods are set up properly first.
Ensure that your Time dimension has a hierarchy for at least
Year>Month
You must also define your attribute relationships
Key>Month>Year

Go to Time dimension>Attribute Relationships>Right click Year Attribute>Properties
Ensure Type is set to Years
This defines the year level for this time hierarchy.

Now your YTD function will work without any errors
The YTD will represent all periods within the yr-month hierarchy up to the currentmember
The SUM will keep adding across each period to get a running total.

-----------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[A YTD Case]
 AS sum(ytd([View B Time].[yr-month].currentmember),[Measures].[A Value]),
VISIBLE = 1;
-----------------------------------------------------------------------------

PRIOR YEAR YTD
To calculate a prior Year YTD uses almost the same formula.
Instead of using the currentmember, we have to use the PARALLELPERIOD of the currentmember one year ago.
PARALLELPERIOD(Year Set Containing your year numbers, 1 year ago, Month set containing the months.the currentmember year)

-------------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[A PYYTD Case]
 AS sum(YTD(
PARALLELPERIOD(
[View B Time].[Yr-Month].[Year Num],
1,
[View B Time].[Yr-Month].currentmember
)
),
[Measures].[A Value]
),
VISIBLE = 1  ;
---------------------------------------------------------------------------------