SSAS How-To: Create Dynamic Time Calculations To Reuse Over Any Measure

Rather than making individual time calculations for each measure in an SSAS cube (i.e. YTD, Prior YTD, YTD Variances, etc), one can use a more dynamic approach to time calcs applicable to all measures and save a great amount of dev time and overhead in the process.

1. Create Named Query in DSV:

SELECT      1 AS DateCalculationKey, ‘Selected Date’ AS DateCalculation
UNION
SELECT      2, ‘YTD’
UNION
SELECT      3, ‘PY YTD’
UNION
SELECT      4, ‘YTD Chg’
UNION
SELECT      5, ‘YTD Chg %’

2. Create the dimension and add it to the Dimension Usage tab of the cube (no relations to the fact will exist)

3. Add the MDX script calculations:

SCOPE (

[Date].[Calendar Hierarchy].MEMBERS);

[Date Calculations].[Date Calculations].[YTD] =
AGGREGATE
(

    {[Date Calculations].[Date Calculations].[Selected Date]} *
     PERIODSTODATE
     (
       [Date].[Calendar Hierarchy].[Calendar Year],
       [Date].[Calendar Hierarchy].CURRENTMEMBER
     )
);

[Date Calculations].[Date Calculations].[PY YTD] =
AGGREGATE
(
   {[Date Calculations].[Date Calculations].[Selected Date]} *
   PERIODSTODATE
   (
      [Date].[Calendar Hierarchy].[Calendar Year],
      PARALLELPERIOD
      (
         [Date].[Calendar Hierarchy].[Calendar Year],
         1,
         [Date].[Calendar Hierarchy].CURRENTMEMBER
   )
)
);

END SCOPE;

SCOPE ([Date].[Calendar Hierarchy].[All]);

[Date Calculations].[Date Calculations].[YTD] = NULL;

[Date Calculations].[Date Calculations].[PY YTD]=NULL;

END SCOPE;

[Date Calculations].[Date Calculations].[YTD Chg]=[Date Calculations].[Date Calculations].[YTD]–

[Date Calculations].[Date Calculations].[PY YTD];

[Date Calculations].[Date Calculations].[YTD Chg %] =
IIF
(
[Date Calculations].[Date Calculations].[PY YTD] = 0,
NULL,
([Date Calculations].[Date Calculations].[YTD] – [Date Calculations].[Date Calculations].[PY YTD]) /
[Date Calculations].[Date Calculations].[PY YTD]
);

FORMAT_STRING([Date Calculations].[Date Calculations].[YTD Chg %]) = “#,0.0%”;

//Create current year and month in scripts:
CREATESET CURRENTCUBE.[Current Year]
AS {(strtomember(“[Date].[Calendar Hierarchy].[Calendar Year].[“+ cstr(year(now()))+”]”))}, DISPLAY_FOLDER = ‘Named Sets’ ;

CREATESET CURRENTCUBE.[Current Month] AS
TAIL
(
NONEMPTY
(
[Date].[Calendar Hierarchy].[Month Name Year],
[Measures].[Fact Sales Count]
)
);

The result!