Yesterday I visited a client and was asked – how do the time intelligence functions handle the fact that February has 29 days in 2020.
Well – in fact there was a few surprises depending on what you select from you date table.
Let’s look as some examples – I will use the following Internet Sales Amount from the years 2011-2013 from Adventure Work Database where we in February 2012 have 29 days.
As you can see, we have the year 2012 where we have 29 days.
In order to calculate Internet Sales Amount LY – I use the following
Internet Sales Amount LY = CALCULATE([Internet Sales Amount];SAMEPERIODLASTYEAR(DimDate[Date]))
Which works fine
But notice the behavior if we put dates or days numbers on the rows
SURPRISE – Internet Sales Amount LY will show the value for the 28th of February 2011 instead of a blank value as you perhaps would expect
If you select year 2013 we will see this
The 29 of feb 2012 will “disappear” but the total for February will include the number.
DATEADD() – last year
If we use the function DATEADD instead – it will work exactly the same way.
IAS LY = CALCULATE([Internet Sales Amount];DATEADD(DimDate[Date];-1;YEAR))
DATEADD() – same day last year
If you want to compare the same Saturday (the 29th of feb 2020 is a Saturday) last year – which is the 2nd of march we can do this by using the same DATEADD function but with different parameters
IAS LY same weekday = CALCULATE([Internet Sales Amount];DATEADD(DimDate[Date];-364;DAY))
This will compare the same day 52 weeks ago (52 * 7 = 364) and there by giving us the value from the 29th of feb 2012 on the 27th of feb 2013.
Now what about the function DATESMTD()
ISA MTD = CALCULATE([Internet Sales Amount];DATESMTD(DimDate[Date]))
ISA MTD LY = CALCULATE([Internet Sales Amount LY];DATESMTD(DimDate[Date]))
These functions will calculate the running total for the month for the given day number
Notice that the ISA MTD works fine in 2012 for the 29th and the LY measure will show the same result for the 28th and 29th in 2012 – and in 2013 it will actually for the 28th show the sum of both the 28 and 29th
You might find that some users find it difficult to understand how the calculations works when the look at dates instead of month totals especially in the case where they will get the same value for LY on both the 28 and 29th in 2012/2020.
If you compare cross years on calendar dates I find the result that SAMEPERIODLASTYEAR() returns makes better sense than leaving it empty/blank but what do you or your users think. Let me know in the comments.
Hope you find this little walkthrough useful.
And remember to ALWAYS use a datetable in your model if you do time intelligence calculations in DAX.
One thought on “DAX Time intelligence and the 29th of February – #PowerBI”
Pingback: DAX and Leap Days – Curated SQL