Power-biHow-ToBeginner · 3 min read

How to Use SAMEPERIODLASTYEAR DAX in Power BI for Year-Over-Year Analysis

Use the SAMEPERIODLASTYEAR function in Power BI DAX to get a date range from the same period last year based on a date column. It is commonly used inside CALCULATE to compare measures year-over-year, like sales or revenue.

📐

Syntax

The SAMEPERIODLASTYEAR function takes one argument, a column of dates, and returns a table of dates shifted exactly one year back.

dates: A column containing dates (usually a date table column).

This function works best with a continuous date column and a proper date table.

DAX

SAMEPERIODLASTYEAR(<dates>)

💻

Example

This example shows how to create a measure that calculates last year's sales for the same period as the current filter context.

DAX

Last Year Sales = CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)

Output

If the current filter context is January 2024, this measure returns the total sales for January 2023.

⚠️

Common Pitfalls

Missing or incomplete date table: SAMEPERIODLASTYEAR requires a continuous date column without gaps.
Using non-date columns: Passing a non-date column causes errors.
Incorrect filter context: If the date column is not properly related or filtered, results may be wrong.

Always ensure your date table is marked as a date table in Power BI and related correctly.

DAX

/* Wrong: Using a non-date column */
Last Year Sales Wrong = CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR(Sales[OrderID])
)

/* Right: Using a proper date column */
Last Year Sales Correct = CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)

📊

Quick Reference

Function	Description	Argument
SAMEPERIODLASTYEAR	Returns dates from the same period last year	(Date column)
CALCULATE	Changes filter context to evaluate an expression	,
SUM	Adds values in a column

✅

Key Takeaways

Use SAMEPERIODLASTYEAR with a proper date column to get last year's matching dates.

Combine SAMEPERIODLASTYEAR inside CALCULATE to compare measures year-over-year.

Ensure your date table is continuous and marked as a date table in Power BI.

Avoid passing non-date columns to SAMEPERIODLASTYEAR to prevent errors.

Check relationships and filter context for accurate year-over-year results.