DATEADD for period shifts in Power BI - Step-by-Step Guide

Introduction

This feature helps you move your data view forward or backward in time. It shifts dates by days, months, quarters, or years. This is useful when you want to compare data from different time periods easily.

When you want to see sales from the same month last year next to this year's sales.

When you need to compare this quarter's revenue with the previous quarter.

When your report should show trends by shifting dates by weeks or months.

When you want to create a moving average that looks back over past periods.

When you want to analyze customer activity by shifting dates to see changes over time.

Steps

Step 1: Open the Power BI Desktop file with your data model

- Power BI Desktop main window

Your report and data model are visible

Step 2: Click on the Modeling tab

- Ribbon at the top

Modeling options appear

Step 3: Click on New measure

- Modeling tab

A formula bar appears to enter a DAX expression

Step 4: Type the DATEADD formula to shift dates, for example: Sales Last Year = CALCULATE(SUM(Sales[Amount]), DATEADD('Calendar'[Date], -1, YEAR))

- Formula bar

A new measure named 'Sales Last Year' is created that sums sales shifted one year back

💡 Make sure your date column is from a proper date table marked as a date table

Step 5: Add a visual like a table or chart to your report

- Report canvas

You see the original sales and the shifted sales side by side for comparison

Before vs After

Before

A table visual shows sales amounts for each month in 2023 only

After

The table visual shows sales amounts for each month in 2023 and a new column with sales from the same month in 2022

Settings Reference

Date column

📍 Fields pane, select your date table

Used as the base for shifting periods in DATEADD

Default: None

Interval

📍 In the DATEADD function formula

Defines the time unit to shift the dates

Default: YEAR

Number of intervals

📍 In the DATEADD function formula

Defines how many intervals to shift (negative for past, positive for future)

Default: -1

Common Mistakes

Using a column that is not a proper date column in DATEADD

DATEADD requires a continuous date column from a date table to work correctly

Use a dedicated date table marked as a date table in Power BI and use its date column

Forgetting to wrap DATEADD inside CALCULATE

DATEADD returns a table of dates, so it must be inside CALCULATE to modify filter context

Always use CALCULATE with DATEADD to shift the filter context for measures

Summary

DATEADD shifts dates by days, months, quarters, or years to compare different time periods.

Use it inside CALCULATE with a proper date column from a date table.

Remember to choose the right interval and number of intervals to shift your data correctly.