Power-biHow-ToBeginner · 3 min read

How to Use DATEADD in DAX in Power BI: Syntax and Examples

In Power BI, use the DATEADD function in DAX to shift dates by a specified number of intervals like days, months, quarters, or years. The syntax is DATEADD(, , ), where is a date column, is how many intervals to move, and is the type of interval such as DAY, MONTH, QUARTER, or YEAR.

📐

Syntax

The DATEADD function moves dates forward or backward by a specified number of intervals. It requires three parts:

<dates>: A column of dates to shift.
<number_of_intervals>: How many intervals to add (positive) or subtract (negative).
<interval>: The type of interval to shift by. It can be DAY, MONTH, QUARTER, or YEAR.

DAX

DATEADD(<dates>, <number_of_intervals>, <interval>)

💻

Example

This example shows how to create a measure that calculates sales from the previous month by shifting the date column one month back.

DAX

Previous Month Sales = CALCULATE(
    SUM(Sales[Amount]),
    DATEADD(Calendar[Date], -1, MONTH)
)

Output

If the current filter context is March 2024, this measure sums sales from February 2024.

⚠️

Common Pitfalls

Using DATEADD on a column that is not a continuous date column can cause errors or unexpected results.
Passing a scalar date value instead of a date column will not work.
Using incorrect interval names like MONTHS instead of MONTH will cause syntax errors.
Not having a proper date table with continuous dates can lead to missing data when shifting dates.

DAX

/* Wrong usage: scalar date instead of date column */
Wrong Measure = DATEADD(TODAY(), -1, MONTH)

/* Correct usage: date column from calendar table */
Correct Measure = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, MONTH))

📊

Quick Reference

Parameter	Description	Example Values
	Column of dates to shift	Calendar[Date]
	Number of intervals to move (positive or negative)	1, -1, 3
	Type of interval to shift by	DAY, MONTH, QUARTER, YEAR

✅

Key Takeaways

Use DATEADD to shift dates by days, months, quarters, or years in DAX.

Always provide a continuous date column as the first argument.

Use positive numbers to move forward and negative to move backward in time.

Common intervals are DAY, MONTH, QUARTER, and YEAR; spelling must be exact.

DATEADD works well with CALCULATE to compare values across time periods.