DATEADD for period shifts in Power BI - Cell-by-Cell Formula Trace

Sample Data

This table shows daily sales data with dates in column A and sales amounts in column B.

Cell	Value
A1	Date
A2	2024-01-01
A3	2024-01-02
A4	2024-01-03
B1	Sales
B2	100
B3	150
B4	200

Formula Trace

Sales_Last_Week = CALCULATE(SUM('Table'[Sales]), DATEADD('Table'[Date], -7, DAY))

Step 1: DATEADD('Table'[Date], -7, DAY)

Step 2: FILTER('Table', 'Table'[Date] IN shifted_dates)

Step 3: SUM('Table'[Sales]) over filtered rows

Cell Reference Map

Date

↓

Sales

The formula references the Date column to shift dates by 7 days back and sums Sales for those shifted dates.

Result

    A          B               C
1 | Date     | Sales | Sales_Last_Week |
2 |2024-01-01|  100  |       0        |
3 |2024-01-02|  150  |       0        |
4 |2024-01-03|  200  |       0        |

Since the sample data only has dates starting at 2024-01-01, shifting by -7 days results in no matching dates, so Sales_Last_Week is 0 for all rows.

Sheet Trace Quiz - 3 Questions

What does DATEADD('Table'[Date], -7, DAY) do in this formula?

A Shifts each date 7 days earlier

B Shifts each date 7 days later

C Filters dates to only the last 7 days

D Returns the original dates without change

Key Result

DATEADD shifts a date column by a specified number of intervals (days, months, years) to enable period comparisons.