Time Intelligence in SSAS

When creating your multidimensional data model in SSAS, quite often you will need to add some time intelligence to your model. That means, your end users would like to see how figures progressed (or regressed) over specific time period, so they can make various predictions or assumptions, or try to understand why, for example, sales decreased.

ParallelPeriod()

One of the most frequent usage of time intelligence is to compare figures from same period last year/quarter/month. In order to achieve that, there is a handy function in SSAS, called PARALLELPERIOD. By definition, this function returns a member from a previous period in the same relative position comparing to specified member. It will be much more clear with real example:

select parallelperiod([Date].[Year - Quarter - Month].[Year]
     ,1
     ,[Date].[Year - Quarter - Month].currentmember)
     ,[Measures].[Sales Amt]

This function simply says: give me value of a measure Sales Amt which is in Date dimension, in a hierarchy Year-Quarter-Month, exactly one (year) position prior to current member value. We can also simply check value for previous quarter, by adjusting this formula to:

select parallelperiod([Date].[Year - Quarter - Month].[Quarter]
	,1
	,[Date].[Year - Quarter - Month].currentmember)
	,[Measures].[Sales Amt]

Of course, you are not restricted only to a previous time frame. You can obtain value from 2 years ago by changing Index value:

select parallelperiod([Date].[Year - Quarter - Month].[Year]
	,2
	,[Date].[Year - Quarter - Month].currentmember)
	,[Measures].[Sales Amt])

In case you need to calculate the divergence between current and previous period, you can easily achieve this with following formula:

([Date].[Year - Quarter - Month].CURRENTMEMBER,[Measures].[Sales Amt]) 
- (parallelperiod([Date].[Year - Quarter - Month].[Year]
	,1
	,[Date].[Year - Quarter - Month].currentmember)
	,[Measures].[Sales Amt])

You can then format FORE_COLOR property of this calculation to green/red color, depending if it is negative value or not. Something like this:

FORE_COLOR = case when ([Date].[Year - Quarter - Month].CURRENTMEMBER,[Measures].[Sales Amt]) 
- (parallelperiod([Date].[Year - Quarter - Month].[Year]
	,1
	,[Date].[Year - Quarter - Month].currentmember)
	,[Measures].[Sales Amt]) < 0 
				then 255 /*Red*/ else 32768 /*Green*/ end

PeriodsToDate()

Another useful function is PeriodsToDate, which comes handy when you need to calculate, for example, year-to-date, quarter-to-date, etc. values.

SUM (
PeriodsTodate(
 [Date].[Year - Quarter - Month - Day].[Year]
,[Date].[Year - Quarter - Month - Day].CurrentMember)
,[Measures].[Sales Amt]
)

This formula will sum up all values from the same level as a specified member (CurrentMember). Similar can be done for quarter, just changing the level from year to quarter:

SUM (
PeriodsTodate(
 [Date].[Year - Quarter - Month - Day].[Quarter]
,[Date].[Year - Quarter - Month - Day].CurrentMember)
,[Measures].[Sales Amt]
)

Conclusion

There are, of course, whole bunch of time intelligence functions in MDX, but these two I’m using on regular basis.

Last Updated on January 16, 2020 by Nikola

Spread the music: