Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 154

Here's an example of how it can be used in a calculated measure:
CREATE MEMBER CURRENTCUBE.Measures.SamePeriodPreviousYearSales AS
(Measures.[Sales Amount],
ParallelPeriod (
[Date Order].[Calendar].[Calendar Year],
1,
[Date Order].[Calendar].CurrentMember)),
FORMAT_STRING = "#,#.00";
Here we pass to ParallelPeriod the Year level, the fact we want to go back 1
period, and as a starting point, the current member of the Date Order dimension's
Calendar hierarchy; the result is that ParallelPeriod returns the same period as the
current member on Calendar but in the previous year.
Having calculated the sales in the same period in the previous year, it is
straightforward to calculate the percentage growth. Note that here we are handling
two different situations where we want to return a NULL value instead of a number:
when there were no sales in the previous period (when it would otherwise show
a useless 100%) and when there are no sales in the current period (when it would
otherwise show a useless -100%).
CREATE MEMBER CURRENTCUBE.Measures.SamePeriodPreviousYearGrowth AS
IIF (
Measures.SamePeriodPreviousYearSales = 0,
NULL,
IIF (
Measures.[Sales Amount] = 0,
NULL,
(Measures.[Sales Amount] - Measures.SamePeriodPreviousYearSales) /
Measures.SamePeriodPreviousYearSales)),
FORMAT_STRING = "Percent";