Power Pivot Principles: Introducing the DATEADD Function
20 November 2018
Welcome back to our Power Pivot blog. Today, we introduce the DATEADD function.
The DATEADD function is a time intelligence function just like the TOTALYTD and DATESYTD functions. They all require a calendar table with strict ascending and contiguous dates with no gaps (read more about calendar tables and contiguous dates here).
The syntax of this function is:
DATEADD(dates, number_of_intervals, interval)
The DATEADD function returns the table of column of dates that is shifted either forward or backwards in time specified by the number_of_intervals.
To illustrate, let’s create a simple measure with the DATEADD function:
The first parameter we have to specify is the dates argument; in this case, we shall use our contiguous date table ‘Date Table’[Dates].
The next parameter is the number_of_intervals. This can be any integer which Power Pivot will use to add or subtract the from the dates. In our example we use ‘1’.
The final parameter to define is the interval. We can specify the interval to be YEAR, QUARTER, MONTH or DAY. In this case, we are looking for the sales one year ago so we shall use YEAR.
Upon checking the formula Power Pivot returns with ‘no errors in formula’. However, after clicking ‘OK’ we receive this error:
This is because we have not specified what value we want Power Pivot to calculate. Including ‘CALCULATE([Sales],’ (with an additional closed bracket at the end) to our formula should suffice:
As long as the dates are contiguous, our measure should work fine now:
Hang on, it seems that our measure is returning with the sales one year in the future not one year ago. Another slight modification to our measure should address this:
Note, if we wish for DATEADD to return with the sales amounts from the past we have to use a negative integer and a positive integer for sales amounts in the future.
That’s it for this week, happy pivoting!
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.
