DATEADD
Shifts the dates in the filter context by a number of days, months, quarters or years.
DATEADD(<dates>, <number_of_intervals>, <interval>), where interval is DAY, MONTH, QUARTER or YEARReturns: A table of dates.
What it does
DATEADD moves each visible date by a set number of intervals. A negative number moves dates back. Finance teams use it to compare a month with the prior month, a quarter with the prior quarter, and a period with the same period a year earlier. Inside CALCULATE, the shifted dates become the new filter, so the measure is recalculated for the earlier period.
Syntax and arguments
- dates: the date column of the Calendar table. Required.
- number_of_intervals: how many intervals to move. Use -1 for one step back, and 1 for one step forward. Required.
- interval: DAY, MONTH, QUARTER or YEAR. Required.
Example
A monthly revenue report needs the prior month and the prior quarter next to the current figure.
Revenue Prior Month = CALCULATE([Total Revenue], DATEADD('Calendar'[Date], -1, MONTH))
Revenue Prior Quarter = CALCULATE([Total Revenue], DATEADD('Calendar'[Date], -1, QUARTER))
In the October 2026 row, Revenue Prior Month adds up September 2026 and Revenue Prior Quarter adds up July through September 2026. Both use the visible dates, so a month row always compares a full month with a full month.
Example: a second, different use
Revenue for the same quarter last year, shown next to the current quarter in a quarterly table.
Revenue Same Quarter LY = CALCULATE([Total Revenue], DATEADD('Calendar'[Date], -1, YEAR))
In a Q4 2026 row, this measure adds up Q4 2025.
Things to watch
- DATEADD moves the visible dates, not whole periods. In the middle of a month, one step back gives the same days of the prior month. Use PARALLELPERIOD when you want the whole prior month instead.
- It needs contiguous dates in the Calendar. Gaps in the date table can give blank or wrong results.
- The result is blank when the prior period has no data, for example in the first months of a new data set.
- Shifting by YEAR works for a fiscal calendar too, because each date moves back one year. It does not return whole fiscal years, so use PREVIOUSYEAR with the year end date for that.
In business terms
DATEADD supports month-over-month and quarter-over-quarter reviews, rolling comparisons, and any check that needs the same period a set distance earlier. Controllers use it in variance commentary, where the question is always "compared with what?"