Power BI Year-over-Year Growth in DAX

Power BI guide Home / Guides / Power BI

Build year-over-year growth measures in Power BI with current-period, prior-year and percentage-change DAX patterns.

2026-06-16 9 min read min read Digital Adaption On this page

1. Confirm the calendar
2. Create YoY measures
3. Adapt for fiscal years

Year-over-year growth is useful only when users trust the period selection. If the date table, fiscal calendar or prior-year logic is loose, the report can show growth that is really a calendar mismatch. For broader reporting repair work, see the Power BI consultancy and ERP reporting analytics pages.

Practical answer

Use one base measure, calculate prior-year value through SAMEPERIODLASTYEAR or DATEADD, then divide the variance by the prior-year value with DIVIDE so blanks and zeroes are handled safely.

1 Confirm the calendar

Before writing the measure, confirm whether the business reports by calendar year, fiscal year or a trading calendar. The model should not hide that decision inside a visual-level filter.

A proper date table lets Power BI shift the current period back one year consistently. It also makes it easier to sort months, build YTD measures and explain why a value changed.

• One active relationship from date table to fact table.
• A full continuous range covering current and previous year.
• Fiscal year columns if the business does not report January to December.
• Month labels sorted by numeric month or fiscal period number.

2 Create YoY measures

The safest pattern separates current value, prior-year value, difference and percentage. That gives you four visible checkpoints while validating the report.

SAMEPERIODLASTYEAR is readable and works well for normal calendars. DATEADD can be easier when you need a consistent shifted period and want to reuse the pattern for months or quarters.

Revenue =
SUM ( Sales[Revenue] )

Revenue Prior Year =
CALCULATE (
    [Revenue],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Revenue YoY Change =
[Revenue] - [Revenue Prior Year]

Revenue YoY Growth % =
DIVIDE ( [Revenue YoY Change], [Revenue Prior Year] )

3 Adapt for fiscal years

If your year starts in April, a calendar-year YoY measure may still calculate but answer the wrong question. Add fiscal period columns and use visuals that filter by fiscal year rather than calendar year.

For manufacturing and distribution teams, this matters when seasonality, price changes and order backlog are reviewed by trading period. A misplaced fiscal boundary can turn a sensible operational trend into a misleading KPI.

1. Add fiscal year and fiscal month number to the date table.
2. Build visuals from fiscal columns, not ad hoc text labels.
3. Compare one fiscal period manually against exported source data.
4. Document the calendar rule in the report notes or data dictionary.

Common mistakes to avoid

Dividing by current year

Growth percentage should normally divide the variance by prior year. Dividing by current year changes the business meaning.

Ignoring zero prior-year values

Use DIVIDE rather than the slash operator so zero or blank prior-year values do not create ugly errors.

Using text month names for order

Alphabetical month sorting makes trends hard to read and can hide obvious reconciliation issues.

Validation checklist

• Prior-year value matches the same filtered period one year earlier.
• YoY percentage is blank where prior year is blank or zero, unless the business wants a defined fallback.
• Fiscal-year visuals use fiscal fields consistently.
• Totals remain explainable when users select multiple periods.
• At least one page includes raw current and prior-year values for auditability.

Need the report to reconcile with ERP?

Digital Adaption helps UK SMEs rebuild trusted Power BI, ERP and operational reporting after migrations, ownership gaps and model drift.

Review reporting trust

FAQ

Can I use DATEADD instead of SAMEPERIODLASTYEAR?

Yes. DATEADD with -1 YEAR is a common alternative and can be reused for month or quarter shifts. The date table still needs to be continuous.

Why is my YoY total different from Excel?

Excel may be comparing exported rows, while Power BI is comparing filter context. Check the selected date range, fiscal calendar and any hidden page filters first.

Start with a 30-minute data risk call