DATEADD (Period Shifts)
Power BI · Advanced DAX & Time Intelligence
Theory
DATEADD(dates, number_of_intervals, interval) shifts the current set of dates by a number of days, months, quarters or years, forwards (positive) or backwards (negative). Inside CALCULATE it lets you compare any period with an earlier or later one: DATEADD('Date'[Date], -1, MONTH) gives the previous month, -1 QUARTER the previous quarter, and -1 YEAR the same as SAMEPERIODLASTYEAR.
For month-over-month growth on 2024 data: January 2024 (₹1,200) is compared with December 2023 (₹1,400), a drop of 14.3%; February (₹2,300) with January, up 91.7%; March (₹2,200) with February, down 4.3%; and April (₹500) with March, down 77.3%. Note that January needs December 2023 from the previous year, which works because filters on Year are replaced by the shifted dates, provided the date table includes 2023 and is marked as a date table. DATEADD requires the dates column of a proper, contiguous date table.
Why we use it
Business reviews compare with the previous month or quarter as often as with last year. DATEADD covers every such shift with one function and one pattern.
When to use it
Use DATEADD for period-over-period comparisons of any length. Use SAMEPERIODLASTYEAR for readability when the shift is exactly one year, and PREVIOUSMONTH when you always want the whole previous month regardless of the dates in context.
How it works
- Visual cell: Feb 2024
- Read the current dates in context
- DATEADD shifts them back one month (Jan 2024)
- CALCULATE evaluates Total Sales on the shifted dates
- Does the shifted period exist in the date table? Yes: Return its value and compute MoM %. No: Return blank.
- Period-over-period comparison
Syntax
DATEADD(<dates>, <number_of_intervals>, <interval>)
interval: DAY | MONTH | QUARTER | YEAR
Prev Month Sales = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH))Example
Prev Month Sales =
CALCULATE(
[Total Sales],
DATEADD('Date'[Date], -1, MONTH)
)
MoM % = DIVIDE([Total Sales] - [Prev Month Sales], [Prev Month Sales])
// Jan 2024: (1,200 - 1,400) / 1,400 = -14.3%
Prev Quarter Sales = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, QUARTER))Key points
- Shifts dates by DAY, MONTH, QUARTER or YEAR
- Negative numbers go back in time, positive forward
- Needs a contiguous, marked date table
- January's previous month comes from the prior year's data
- DATEADD(dates, -1, YEAR) matches SAMEPERIODLASTYEAR