Categories

# 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 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].&
,[Date].[Calendar].[Date].&
}
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 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. 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: 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].&
,[Date].[Calendar PP Demo].[Date].&
}
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  Pay attention to your hierarchies when creating these types of measures and consider edge cases 