Power Pivot Principles: Problems with 1 Month Ago – DATEADD vs. PREVIOUSMONTH
1 December 2020
Welcome back to the Power Pivot Principles blog. This week, we take a look back at, er, looking back and an associated classic gotcha.
Let’s consider a very simple dataset:
Having called the table ‘Sales’, I will add it to the Data Model by clicking in the Table and going to the ‘Power Pivot’ tab and clicking on ‘Add to Data Model’, viz.
Given this is all my data is, I see no reason to create a Calendar Table. However, I will create a measure for my sales, which I will call Total Sales:
Total Sales = SUM(Sales[Sales])
All I want to do is compare these sales with the sales from the previous month. We have discussed the DATEADD function before:
Total Sales 1 Month Ago = CALCULATE([Total Sales],DATEADD(Sales[Month],-1,MONTH))
There is a problem though:
What!? Why aren’t some months showing? This was not the intention at all. For those of you who think this would all be fixed by using a Calendar Table, it might, depending upon how the data was stored – but the issue is essentially a little simpler than that.
Imagine our data was all collected on the 29th of each month instead, i.e.
In this instance, our Total Sales 1 Month Ago would work as envisaged:
The problem is DATEADD(Sales[Month],-1,MONTH) means take the date PRECISELY one month ago. Given we do not have contiguous data, our original calculations were drawing a blank when there were no sales for the corresponding day of the month for the previous month. This is a classic “gotcha” and one you must look out for.
Incidentally, there is a way around this using the original data. Instead of using DATEADD, we use the PREVIOUSMONTH function instead, viz.
Total Sales 1 Month Ago = CALCULATE([Total Sales],PREVIOUSMONTH(Sales[Month]))
This provides the desired result:
DATEADD(Date,-1,MONTH) and PREVIOUSMONTH are not quite the same thing. Watch out!
That’s it for this week!
Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
