A common request from business users is ‘Time Intelligence’ functionality in an SSAS cube.  For example, users may wish to compare current year sales with the prior year or calculate sales year-to-date.  DAX includes a wide range of time intelligence functions to perform these types of calculations.  We can create measures in a tabular cube for the various time intelligence formulas, such as Sales Year-Over-Year or Sales Year-to-Date.  An alternative to exposing individual measures for the various time intelligence formulas is to create a ‘Time Calculations’ dimension.

To implement a ‘Time Calculations’ dimension in a tabular cube, we first need to add a table to the Tabular model to store the various time-intelligence options.  The table consists of two columns, the first column is a key column that is an auto-incrementing integer.  In this example, the column is named ‘Time Calculations Key’ and is hidden from client tools.  The second column, named ‘Time Calculations’, stores the various time-intelligence options as text.  The table for this example is shown below:

Once you have added the ‘Time Calculations’ table to your tabular model, the next step is to implement measures that check the ‘Time Calculations’ value selected and evaluate a DAX expression to return the correct value.  An example measure named ‘Retail Sales Amount’ is displayed below:

Retail Sales Amount:=IF(HASONEVALUE(‘Time Calculations’[Time Calculations]), SWITCH(VALUES(‘Time Calculations’[Time Calculations]), “Current Period”, [Retail Sales Amount Current Period], “Current Period LY”, [Retail Sales Amount Current Period LY], “Current Period LY Chg”, [Retail Sales Amount Current Period LY Chg], “Current Period LY Chg %”, [Retail Sales Amount Current Period LY Chg %],”MTD”,[Retail Sales Amount MTD],”MTD LY”,[Retail Sales Amount MTD LY],”MTD LY Chg”,[Retail Sales Amount MTD LY Chg],”MTD LY Chg %”,[Retail Sales Amount MTD LY Chg %],”YTD”,[Retail Sales Amount YTD],”YTD LY”,[Retail Sales Amount YTD LY],”YTD LY Chg”,[Retail Sales Amount YTD LY Chg],”YTD LY Chg %”,[Retail Sales Amount YTD LY Chg %], [Retail Sales Amount Current Period]), [Retail Sales Amount Current Period])

The DAX expression first checks if one value was selected from the ‘Time Calculations’[Time Calculations'] field.  If one value is selected, then the expression compares the selected value to the various ‘Time Calculation’ options (“Current Period”, ‘Current Period LY”, “Current Period LY Chg”, etc.).  If a match is found, it returns the correct measure that corresponds to the time calculation selected.  If no match is found, then the expression returns the measure for the “Current Period” (another option is to return BLANK()).

The measures returned by the DAX expression above should be hidden from client tools.  The expressions for these measures are shown below:

Retail Sales Amount Current Period:=SUM([Retail Sales Amount])

Retail Sales Amount Current Period LY:=CALCULATE([Retail Sales Amount Current Period],SAMEPERIODLASTYEAR(‘Date’[Date]))

Retail Sales Amount Current Period LY Chg:=[Retail Sales Amount Current Period] – [Retail Sales Amount Current Period LY]

Retail Sales Amount Current Period LY Chg %:=IF(ISBLANK([Retail Sales Amount Current Period LY]) || [Retail Sales Amount Current Period LY] = 0,BLANK(),[Retail Sales Amount Current Period LY Chg] / [Retail Sales Amount Current Period LY])

Retail Sales Amount MTD:=TOTALMTD([Retail Sales Amount Current Period],’Date’[Date])

Retail Sales Amount MTD LY:=CALCULATE([Retail Sales Amount Current Period],SAMEPERIODLASTYEAR(DATESMTD(‘Date’[Date])))

Retail Sales Amount MTD LY Chg:=[Retail Sales Amount MTD] – [Retail Sales Amount MTD LY]

Retail Sales Amount MTD LY Chg %:=IF(ISBLANK([Retail Sales Amount MTD LY]) || [Retail Sales Amount MTD LY] = 0,BLANK(),[Retail Sales Amount MTD LY Chg] / [Retail Sales Amount MTD LY])

Retail Sales Amount YTD:=TOTALYTD([Retail Sales Amount Current Period],’Date’[Date])

Retail Sales Amount YTD LY:=CALCULATE([Retail Sales Amount Current Period],SAMEPERIODLASTYEAR(DATESYTD(‘Date’[Date])))

Retail Sales Amount YTD LY Chg:=[Retail Sales Amount YTD] – [Retail Sales Amount YTD LY]

Retail Sales Amount YTD LY Chg %:=IF(ISBLANK([Retail Sales Amount YTD LY]) || [Retail Sales Amount YTD LY] = 0,BLANK(),[Retail Sales Amount YTD LY Chg] / [Retail Sales Amount YTD LY])

This implementation has a few drawbacks:

• The format of the measure (in this example ‘Retail Sales Amount’) cannot change based on the option selected.  The options that specify a percentage cannot be displayed as a percentage since you can only define a single number format for a measure in Tabular.  In some client and reporting tools (such as Excel and SSRS), you have the flexibility to modify the format of the measure but this is not the case with other tools.  This is, in my opinion, the biggest drawback.
• Implementing the ‘Time Calculations’ logic for multiple measures requires replicating similar code for every measure, which is time consuming and error prone.

You will need to compare the pros and cons of exposing the time calculations as individual measures versus implementing a ‘Time Calculations’ dimension and select the option that best meets your end-users needs.  If you are implementing a small number of time calculation measures, it may make more sense to just expose all the measures to the user.  If the list is quite lengthy, then a ‘Time Calculations’ dimension may be preferable.