Daily DAX : Day 344 DATEADD

Power BI DAX DATEADD Function

Description

The DATEADD function in Power BI DAX (Data Analysis Expressions) shifts a date/time column by a specified number of intervals (e.g., days, months, years) to create a new date. It is commonly used for time-based calculations, such as comparing sales across different periods or creating rolling date ranges.

Syntax

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

• <dates>: A column containing date/time values.
• <number_of_intervals>: An integer specifying the number of intervals to add (positive) or subtract (negative).
• <interval>: The time unit for the shift. Options are:
  • YEAR
  • QUARTER
  • MONTH
  • DAY

Return Value

A column of dates shifted by the specified interval.

Use Cases

• Year-over-Year Analysis: Calculate sales for the same period in the previous year.
• Rolling Periods: Create measures for a rolling 12-month period.
• Forecasting: Shift dates forward to project future dates.
• Period Comparisons: Compare data from the previous month, quarter, or year.

Example

Suppose you have a table Sales with a Date column and want to calculate sales from the previous year.

PreviousYearSales = 
CALCULATE(
    SUM(Sales[Amount]),
    DATEADD(Sales[Date], -1, YEAR)
)
        

This measure sums the Amount column for dates shifted back by one year. For example, if the current date is 2025-09-22, it returns sales for 2024-09-22.

Notes

• DATEADD requires a contiguous date range in the dates column to work correctly.
• It works only with a date/time column, not a single date value.
• Use in combination with CALCULATE to filter data based on the shifted dates.