Power Pivot Principles: DATEADD Comparison
27 April 2021
Welcome back to the Power Pivot Principles blog. This week, we are going to work on an alternate approach from last week to compare monthly data.
While conducting my analysis for building the financial performance report of company XYZ, I decided to review the monthly changes in some of the line items. Out of the options I had in mind, let me show you the one that is my favourite.
Let me use monthly Sales as an example by pulling it in a table:
Now to analyse month on month performance, let us compare it to the preceding month. To calculate this, I will use the function DATEADD. I believe it is one of the most versatile time series functions. The syntax is as follows:
DATEADD(dates, number_of_intervals, interval)
I can simply use this to and create the following measure:
=CALCULATE([Sales (including GST)], DATEADD('Calendar'[Date], -1, MONTH))
Notice how I use the CALCULATE function: using DATEADD alone would serve no purpose. Now I simply pull this measure in a field to generate the following results:
Yes, it was as simple as that! Now we can easily compare each month’s sale to its preceding month.
That’s it for this week!
Come back next week to see how we use the disconnected table for performing a sensitivity analysis by staying 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.
