Period over Period calculations, which compare the results of one time period against a previous time period, are extremely useful in the financial world. Everyone’s interested in growth since last month/quarter/year. Most MDX implementations that I’ve seen use the ParallelPeriod function, which
Returns a member from a prior period in the same relative position as a specified member
The typical approach would be to create separate Year over Year, Quarter over Quarter, and Month over Month calculations. For example, to create a Year over Year calculation for the previous year:
This script first calculates the amount for the current member of the hierarchy:
([Date].[Calendar].CurrentMember, [Measures].[Amount])
Then finds the previous member of this of a specific level in this hierarchy using the ParallelPeriod() function, and subtract that from the current member:
(ParallelPeriod(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Amount])
Here’s how that looks in excel, along with a demonstration of what happens if you try to use this calculations with other levels in the date dimension:
In essence, using this approach means we’d have to create a calculation for every level of the date hierarchy; this is tedious and a ton of maintenance for what amounts the same logic. Besides, it would more user-friendly to have one calculation that works for ALL levels in a natural date hierarchy. But how can this be done if the first parameter for ParallelPeriod() must accept a specific level argument? The trick here is to use .LEVEL property to dynamically return the level of the current member in the hierarchy:
([Date].[Calendar].CurrentMember, [Measures].[Amount])
-
(ParallelPeriod(
[Date].[Calendar].CurrentMember.Level,
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Amount])
Here’s how that looks in excel:
Lastly, since this calculation exclusively looks at the previous member of the hierarchy (previous year, previous month, etc), we can write the MDX expression even more elegantly using the PrevMember property:
([Date].[Calendar].CURRENTMEMBER,[Measures].[Amount])-
([Date].[Calendar].CURRENTMEMBER.PREVMEMBER,[Measures].[Amount])