How to Create YOY Calculation DAX in Power BI

To create a Year-Over-Year (YOY) calculation in Power BI, use the CALCULATE function combined with SAMEPERIODLASTYEAR inside a measure. This shifts the date context to the previous year, allowing you to compare current year values with last year easily.

Syntax

The basic syntax for a YOY calculation in DAX is:

YOY Measure = CALCULATE(
    [Base Measure],
    SAMEPERIODLASTYEAR('Date'[Date])
)

Here:

• [Base Measure] is the measure you want to compare year over year, like total sales.
• CALCULATE changes the filter context to the previous year.
• SAMEPERIODLASTYEAR shifts the date filter to the same period last year based on the 'Date' table.

DAX

YOY Measure = CALCULATE(
    [Base Measure],
    SAMEPERIODLASTYEAR('Date'[Date])
)

Example

This example shows how to create a YOY Sales measure comparing current year sales to last year sales.

DAX

Total Sales = SUM('Sales'[SalesAmount])

YOY Sales = CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

Output

If current year sales are $100,000 and last year sales are $90,000, the YOY Sales measure will return $90,000 when filtered by current year dates.

Common Pitfalls

Common mistakes when creating YOY calculations include:

• Not having a proper continuous Date table marked as a date table in Power BI.
• Using SAMEPERIODLASTYEAR without a proper date column causes errors or wrong results.
• Forgetting to use CALCULATE to change filter context.
• Using YEAR functions instead of time intelligence functions, which can break dynamic filtering.

Correct approach uses a continuous date table and time intelligence functions like SAMEPERIODLASTYEAR.

DAX

/* Wrong way: Using YEAR filter manually */
YOY Sales Wrong = CALCULATE(
    [Total Sales],
    FILTER(
        ALL('Date'),
        YEAR('Date'[Date]) = YEAR(TODAY()) - 1
    )
)

/* Right way: Using SAMEPERIODLASTYEAR */
YOY Sales = CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

Quick Reference

Function	Purpose	Example Usage
CALCULATE	Changes filter context to evaluate expression	CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
SAMEPERIODLASTYEAR	Returns dates from the same period last year	SAMEPERIODLASTYEAR('Date'[Date])
SUM	Adds up values in a column	SUM('Sales'[SalesAmount])
ALL	Removes filters from a table or column	ALL('Date')

Key Takeaways

Use CALCULATE with SAMEPERIODLASTYEAR to create YOY measures in Power BI.

Ensure you have a continuous Date table marked as a date table for time intelligence functions to work.

Avoid manual year filtering; use built-in time intelligence functions for dynamic and accurate results.

YOY calculations compare current period values with the same period in the previous year automatically.

Test your measures with sample data to confirm correct YOY results.