…brew your own Business Intelligence

MDX: ParallelPeriod Is Not Calendar Aware (Part 2)

In the last post, we explored the ParallelPeriod function in a bit of detail and then used a contrived example to highlight a particular edge case that needs to be explicitly handled when the number of children differs between the 2 periods. Specifically, we looked at a prior month calculation and showed that calculating the same date in the prior month for 3/31/2007 returns NULL. As we concluded, this is because ParallelPeriod works by calculating the same relative position (like the Cousin function) and there’s not a corresponding 31 date in the previous month (February).

To expand upon our understanding of the ParallelPeriod function, let’s consider how this edge-case works at the year-level. As we all know, years have 365 days…except for leap years which have 366 days and occur every 4 years (e.g. 2004, 2008, 2012, etc).

WITH
    SET Years AS [Date].[Calendar].[Calendar Year]
    MEMBER DaysInYear AS
        DESCENDANTS(
             [Date].[Calendar].CurrentMember
            ,[Date].[Calendar].[Date]
        ).Count
SELECT    {
             DaysInYear
        } ON 0,
        {
            Years
        } ON 1
FROM    [Adventure Works]

image

Based on our understanding from the previous post, it seems obvious that, in addition to explicitly handling the scenario where different months have different number of days, we will also need to handle the scenario where different years have different number of days.

But is that the case?

Consider the following query which takes a specific set of dates and calculates the same date in the prior year:

WITH 
    SET LastDateOfYear AS 
        {
             [Date].[Calendar].[Date].&[20071231]
            ,[Date].[Calendar].[Date].&[20081231]
        }
    MEMBER CurrentDate AS
        [Date].[Calendar].CurrentMember.MemberValue
    MEMBER PriorYear_ParallelPeriod AS 
        ParallelPeriod(
             [Date].[Calendar].[Calendar Year]
            ,1
            ,[Date].[Calendar].CurrentMember
        ).MemberValue
    MEMBER PriorYear_Cousin AS 
        Cousin(
             [Date].[Calendar].CurrentMember
            ,Ancestor(
                 [Date].[Calendar].CurrentMember
                ,[Date].[Calendar].[Calendar Year]
            ).Lag(1)
        ).MemberValue
SELECT    {
             CurrentDate
            ,PriorYear_ParallelPeriod
            ,PriorYear_Cousin
        } ON 0,
        {
            LastDateOfYear
        } ON 1
FROM    [Adventure Works]

image

Not what you expected is it? The specific dates are 12/31/2007 and 12/31/2008 – the last days of the 2007 and 2008. And 2008 is a leap year with 366 days while 2007 is a regular year with 365 days. And based on what we saw in the previous post, you were probably expecting the prior year calculation of 12/31/2008 to return NULL since the previous year (2007) doesn’t have a 366th member.

There’s a bit more to it

Well, it turns out, there’s a bit more to the ParallelPeriod function. The calendar hierarchy upon which we’ve based the all of our calculations thus far has several levels between Date and Year.

image

This comes into play when we get to the part where we need to calculate “relative position”. So we take the specified member (e.g. 12/31/2008), go up to the year level (e.g. 2008), and then back to the previous year (e.g. 2007)…here…at this point, it is time to calculate the member with the same relative position as the originally specified member (e.g. 12/31/2008). However, the original position of the specified member is taken in the context of the next level up…and is therefore 31 (as in the 31st day of December) and NOT 366 (as in the 366th day of 2008) as we might have expected.

To help illustrate this, I’ve created a new user hierarchy with 2 levels:

image

Now, if we port our previous MDX query over to this new hierarchy, we get results which we might have expected from the get got:

WITH 
    SET LastDateOfYear AS 
        {
             [Date].[Calendar PP Demo].[Date].&[20071231]
            ,[Date].[Calendar PP Demo].[Date].&[20081231]
        }
    MEMBER CurrentDate AS
        [Date].[Calendar PP Demo].CurrentMember.MemberValue
    MEMBER PriorYear AS 
        ParallelPeriod(
             [Date].[Calendar PP Demo].[Calendar Year]
            ,1
            ,[Date].[Calendar PP Demo].CurrentMember
        ).MemberValue
    MEMBER NumSiblings AS [Date].[Calendar PP Demo].CurrentMember.Siblings.Count
SELECT    {
             CurrentDate
            ,PriorYear
            ,NumSiblings
        } ON 0,
        {
            LastDateOfYear
        } ON 1
FROM    [Adventure Works]

image

This last MDX query (based on the Calendar PP Demo hierarchy) has an additional calculation (NumSiblings) to show the number of siblings of the current member. If we add this calculation to the original MDX (based on the original Calendar hierarchy) we see the difference:

image

Keep in mind, you’ll still run into issues with parallelperiod, even at the year level for leap-years…but only in February.

Take Away

Pay attention to your hierarchies when creating these types of measures and consider edge cases Winking smile

Leave a Reply