Difficulty: Hard | Concepts: DAX Time Intelligence, SAMEPERIODLASTYEAR, CALCULATE, Date Table

Year-over-year growth in Power BI is one of the most requested visuals in any sales or finance dashboard — and it's a very common Power BI DAX interview question, because it tests whether you understand DAX time intelligence functions and filter context, not just formula syntax.

DAX YoY Growth — Problem Statement

You have a Sales fact table and a proper Date dimension table. Write a DAX measure that calculates Year-over-Year (YoY) sales growth percentage, comparing each period's sales to the same period one year earlier.

Sample Data Model

Fact_Sales
--------------------------------
OrderDate     Sales_Amount
2025-06-15    45000
2025-06-20    32000
2026-06-15    58000
2026-06-20    41000

Dim_Date
--------------------------------
Date          Year    Month
(marked as the model's official Date table)

Expected Output

Year	Total Sales	Sales PY	YoY Growth %
2025	77,000	(blank)	(blank)
2026	99,000	77,000	28.57%

DAX Solution

Total Sales =
SUM ( Fact_Sales[Sales_Amount] )

Sales PY =
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR ( Dim_Date[Date] )
)

YoY Growth % =
DIVIDE (
    [Total Sales] - [Sales PY],
    [Sales PY]
)

Explanation

The key to this measure is SAMEPERIODLASTYEAR(), a DAX time intelligence function that shifts the current filter context on the Date table back by exactly one year, while keeping the same period length (day, month, quarter, or year) — as long as the Date table is marked as a proper date table with a continuous, unbroken date range.

Wrapping that shifted date filter inside CALCULATE() re-evaluates [Total Sales] under that new filter context, giving you last year's number without writing a single row-by-row loop. Finally, DIVIDE() is used instead of a plain / operator specifically to safely return a blank instead of an error when [Sales PY] is zero or blank — a detail interviewers frequently check for, since it's a common cause of "#DIV/0" style errors in production Power BI reports.

Visual Representation

Filter context: Year = 2026 | SAMEPERIODLASTYEAR shifts the Date filter back exactly 1 year | Filter context: Year = 2025 | CALCULATE re-evaluates [Total Sales] under this shifted filter | Sales PY = 77,000 YoY Growth % = (99,000 - 77,000) / 77,000 = 28.57%

Common Mistake to Avoid

A very frequent error in this DAX interview question is applying SAMEPERIODLASTYEAR() directly on a column from the fact table instead of the marked Date dimension table. DAX time intelligence functions require a proper, continuous date column from a table marked as the model's official date table — using the fact table's transaction date column directly can silently produce incorrect or blank results if there are gaps in the dates.

Conclusion

Mastering SAMEPERIODLASTYEAR, CALCULATE, and DIVIDE together is essential for any Power BI developer interview, since this exact pattern extends naturally to related time intelligence measures like Month-over-Month growth, Quarter-over-Quarter comparisons, and rolling 12-month totals — all built on the same underlying filter-context-shifting technique.