A few days ago (exactly one day after 29th of February when testing data from last night’s load) I experienced a strange issue which I couldn’t explain at first but then found out that it is related to the leap year and to the fact that even though this year has a 29th in Feb but last/next does not!
So be careful when using ParallelPeriod on 29 February in leap years!
ParallelPeriod(
[Date].[Y-M-D Calendar].[Month],
-12,
[Date].[Y-M-D Calendar].[Date].&[20120229]
)
This above expression points (would point) on a day exactly one year from now in the future.
Explicitly the command says: based on 2012 02 29 look for the day that will come 12 months later!
As you can see 2 levels and 3 members of the date dimension is involved:
- Date level: 20120219 as the specified member which is the basis of the date difference calculation
- Date level: the end result (which theoretically is 29 Feb in 2013 – but that does not exist!)
- Month level: the difference is defined in months.
When this becomes problematic is when you want to e.g. sum up values across a daterange:
Aggregate the [Sales] measure for the last 12 months from today!
That is a set and literally it means from 2011.02.29. to 2012.02.29. But since 2011.02.29. does not exist the expression is evaluated like using the beginning of the date dimension, e.g. 2000 Jan 1. So be careful!
When writing this post I thought of trying the same with T-SQL:
SELECT DATEADD(Year, 1, '2012 February 29') or SELECT DATEADD(Month, 12, '2012 February 29')
The result of both statements is
2013-02-28 00:00:00.000
Though this is not 100% correct but a lot better than returning data from the beginning of time…
A blog about my challenges in the Microsoft data space 