MDX: ParallelPeriod Is Not Calendar Aware

ParallelPeriod is one of the more commonly used MDX functions out there. It’s primary use is in “prior period” calculations such as Prior Year sales (which in turn can be used to calculate Year-over-Year Change in sales) as well as Rolling Averages:

Create Member CurrentCube.[Measures].[Internet Sales Amount - Prior Year] As
    (
        ParallelPeriod(
             [Date].[Calendar].[Calendar Year]
            ,1
            ,[Date].[Calendar].CurrentMember
        )
        ,[Measures].[Internet Sales Amount]
    )
,Format_String = "Currency"
,Associated_Measure_Group = 'Internet Sales';

Create Member CurrentCube.[Measures].[Internet Sales Amount - YoY Change] As
    (
        [Measures].[Internet Sales Amount] - [Measures].[Internet Sales Amount - Prior Year]
    )
,Format_String = "Currency"
,Associated_Measure_Group = 'Internet Sales';

Create Member CurrentCube.[Measures].[Internet Sales Amount - YoY Pct Change] As
    (
        [Measures].[Internet Sales Amount - YoY Change]
        /
        [Measures].[Internet Sales Amount]
    )
,Format_String = "Percent"
,Associated_Measure_Group = 'Internet Sales';

Note: correction was made to Internet Sales Amount – YoY Change (see comments for details) which is not accurately reflected in the screenshot above.

These types of calculations prove handy when building KPI-style metrics on dashboards and reports where senior management is reviewing period over period performance.

ParallelPeriod Is Not “Calendar Aware”

Recently, a fellow MDX developer shared a tip with the rest of the team: ParallelPeriod is not “Calendar Aware”. For example, it does not keep track of which months have 31 days and which months have 30 or 28 days. Instead it works by tracking how many “members” are on each “level” in a user hierarchy.

As mentioned in the books online page we could have written the Prior Year calculation from above without using the ParallelPeriod function at all:

Create Member CurrentCube.[Measures].[Internet Sales Amount - Prior Year v2] As
    (
        Cousin(
             [Date].[Calendar].CurrentMember
            ,Ancestor(
                 [Date].[Calendar].CurrentMember
                ,[Date].[Calendar].[Calendar Year]
             ).Lag(1)
        )
        ,[Measures].[Internet Sales Amount]
    )
,Format_String = "Currency"
,Associated_Measure_Group = 'Internet Sales';

This form makes it a bit more clear that the operations involved are generic hierarchy traversal using Ancestor (up), Lag (backwards), and Cousin (same relative position under the parent level). The result is to return a prior member in an ordered list of members by going “up and over”. To prove this further, we can write a similar measure based on the product category hierarchy in AdventureWorks:

 

Create Member CurrentCube.[Measures].[Internet Sales Amount - Prior Product Category] As
    (
        ParallelPeriod(
             [Product].[Product Categories].[Category]
            ,1
            ,[Product].[Product Categories].CurrentMember
        )
        ,[Measures].[Internet Sales Amount]
    )
,Format_String = "Currency"
,Associated_Measure_Group = 'Internet Sales';

The result is rather meaningless, but it is functionally equivalent to the Calendar example from earlier…

As you can see above, the calculated measure shows $39,591 for the Road Bikes subcategory which is the second member in the Bikes category. This value lines up with the base measure for the Bike Stands subcategory which just happens to be the second member in the previous category (Accessories).

Meat and Potatoes

Ok – that’s pretty cool right. With that out of the way, it’s time to get to the real point of the post which is this: because ParallelPeriod is not “Calendar Aware” you must pay attention and explicitly handle situations where parent groups (e.g. Months) have a different number of children (e.g. Days).

Consider the example where we want to find the same date in the previous month:

WITH 
    SET DaysInMarch AS 
        {
             [Date].[Calendar].[Date].&[20070328]
            ,[Date].[Calendar].[Date].&[20070329]
            ,[Date].[Calendar].[Date].&[20070330]
            ,[Date].[Calendar].[Date].&[20070331]
            ,[Date].[Calendar].[Date].&[20080328]
            ,[Date].[Calendar].[Date].&[20080329]
            ,[Date].[Calendar].[Date].&[20080330]
            ,[Date].[Calendar].[Date].&[20080331]
            ,[Date].[Calendar].[Date].&[20080530]
            ,[Date].[Calendar].[Date].&[20080531]
        }
    MEMBER CurrentMonth AS
        [Date].[Calendar].CurrentMember.MemberValue
    MEMBER PriorMonth_ParallelPeriod AS 
        ParallelPeriod(
             [Date].[Calendar].[Month]
            ,1
            ,[Date].[Calendar].CurrentMember
        ).MemberValue
    MEMBER PriorMonth_Cousin AS 
        Cousin(
             [Date].[Calendar].CurrentMember
            ,Ancestor(
                 [Date].[Calendar].CurrentMember
                ,[Date].[Calendar].[Month]
            ).Lag(1)
        ).MemberValue
SELECT    {
             CurrentMonth
            ,PriorMonth_ParallelPeriod
            ,PriorMonth_Cousin
        } ON 0,
        {
            DaysInMarch
        } ON 1
FROM    [Adventure Works]

As you can see, I’ve calculated this using ParallelPeriod and again using Cousing-Ancestor-Lag method from above. I’ve also purposely selected a few disjoint ranges of dates to prove a point…and here are the results:

Notice all the NULL values? Those appear because the dates from the “current month” don’t exist in the “prior month”. For example, February ends at day 28 in 2007…so for the “current month” of March, there’s no member in the same “relative position” for days 29, 30, and 31. And then for that same date range in 2008 have a slightly different result where now 2/29 shows up instead of NULL – because it’s a leap day and 2008 was a leap year. It makes perfect sense in this context, however, it could quickly lead to some unexpected values and confuse your business users if you’re not careful to handle this situation from the start.

There are a few ways to handle this scenario, but you will ultimately need to run this by the business and get confirmation on how they expect it to be handled. One method is to simply set it to the last date of the previous period:

MEMBER PriorMonth_v2 AS 
    IIF(
         IsEmpty(ParallelPeriod([Date].[Calendar].[Month],1,[Date].[Calendar].CurrentMember))
        ,Ancestor([Date].[Calendar].CurrentMember,[Date].[Calendar].[Month]).Lag(1).LastChild.MemberValue
        ,ParallelPeriod([Date].[Calendar].[Month],1,[Date].[Calendar].CurrentMember).MemberValue
    )

In this case, we now get valid values where we previously had NULL values. This is usually preferable to the alternative.

 

While the example we explored in this post dealt specifically with a prior month calculation, please keep in mind that the issue is generic and can arise in a handful of other scenarios: future period, N-months ago, etc.