SAMEPERIODLASTYEAR in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sales data for three days in 2023 and the same days in 2022.

Cell	Value
A1	Date
B1	Sales
A2	2023-01-01
B2	100
A3	2023-01-02
B3	150
A4	2023-01-03
B4	200
A5	2022-01-01
B5	80
A6	2022-01-02
B6	120
A7	2022-01-03
B7	180

Formula Trace

CALCULATE(SUM(Sales[Sales]), SAMEPERIODLASTYEAR(Calendar[Date]))

Step 1: SUM(Sales[Sales])

Step 2: SAMEPERIODLASTYEAR(Calendar[Date]) for dates 2023-01-01 to 2023-01-03

Step 3: SUM(Sales[Sales]) filtered by SAMEPERIODLASTYEAR dates

Step 4: CALCULATE(SUM(Sales[Sales]), SAMEPERIODLASTYEAR(Calendar[Date]))

Cell Reference Map

     A           B
1  Date       Sales
2  2023-01-01  100
3  2023-01-02  150
4  2023-01-03  200
5  2022-01-01   80
6  2022-01-02  120
7  2022-01-03  180

Formula references Sales[Sales] in column B and Calendar[Date] in column A.
SAMEPERIODLASTYEAR uses dates in A2:A4 to find matching dates in A5:A7.

The formula uses the Sales column for summing and the Date column for filtering dates one year back.

Result

     A           B           C
1  Date       Sales      Result
2  2023-01-01  100        380
3  2023-01-02  150
4  2023-01-03  200
5  2022-01-01   80
6  2022-01-02  120
7  2022-01-03  180

The result 380 appears next to the 2023 dates, showing total sales for the same period last year (2022).

Sheet Trace Quiz - 3 Questions

What does SAMEPERIODLASTYEAR(Calendar[Date]) return when applied to dates 2023-01-01 to 2023-01-03?

A Dates 2024-01-01 to 2024-01-03

B Dates 2023-01-01 to 2023-01-03

C Dates 2022-01-01 to 2022-01-03

D All dates in 2022

Key Result

SAMEPERIODLASTYEAR shifts a date range exactly one year back for time comparison.