The DAX Time Intelligence Mistake That Silently Breaks at Fiscal Year Boundaries

A DAX Year-over-Year measure can pass every test thrown at it for eleven months straight, then quietly return the wrong comparison the moment the fiscal year rolls over — not because the formula is broken, but because it was never actually fiscal-aware to begin with.

THE COMMON MISTAKE

YoY Sales = 
VAR PriorYear = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    SUM(Sales[Amount]) - PriorYear

SAMEPERIODLASTYEAR is a built-in DAX time intelligence function — and it assumes a standard calendar year, January through December. If your organization runs on a fiscal year starting in April, July, or any month other than January, this function's internal logic shifts dates back exactly 365/366 days on the calendar, not back one fiscal year on your organization's actual calendar. For most of the fiscal year, calendar-year and fiscal-year math happen to align well enough that nobody notices. Right around the fiscal year boundary, they diverge.

WHY THIS PASSES TESTING MOST OF THE YEAR

If your fiscal year starts in April, a YoY comparison for, say, fiscal month 8 (November) shifts back to the correct prior-year November either way — calendar year and fiscal year agree on what "one year ago" means for dates comfortably in the middle of the year. The mismatch only surfaces for dates near the fiscal year boundary, where "same period last year" on a fiscal calendar and "365 days ago" on a standard calendar stop being the same date range. A measure built and tested in June looks completely correct — because June isn't anywhere near the boundary where the bug lives.

THE FIX: BUILD TIME INTELLIGENCE ON A MARKED FISCAL DATE TABLE

Standard DAX time intelligence functions require a proper date table marked as the model's official date table, with a contiguous, gap-free date range — but they still assume calendar-year logic unless you either shift the underlying dates or use fiscal-specific alternatives.

YoY Sales (Fiscal) = 
VAR PriorYear = 
    CALCULATE(
        SUM(Sales[Amount]), 
        DATEADD('Date'[Date], -1, YEAR)
    )
RETURN
    SUM(Sales[Amount]) - PriorYear

Even DATEADD with YEAR still operates on calendar-year logic by default. For genuine fiscal-year comparisons, the more reliable pattern uses your date table's fiscal year and fiscal period columns directly, rather than a built-in function assuming January-start:

YoY Sales (Fiscal Explicit) = 
VAR CurrentFiscalYear = MAX('Date'[FiscalYear])
VAR CurrentFiscalPeriod = MAX('Date'[FiscalPeriod])
VAR PriorYear = 
    CALCULATE(
        SUM(Sales[Amount]),
        FILTER(
            ALL('Date'),
            'Date'[FiscalYear] = CurrentFiscalYear - 1 && 
            'Date'[FiscalPeriod] = CurrentFiscalPeriod
        )
    )
RETURN
    SUM(Sales[Amount]) - PriorYear

This version compares fiscal period to fiscal period directly off explicit columns in the date table, rather than relying on a calendar-year assumption baked into the built-in function.

WHERE THIS SHOWED UP

On a reporting model for an organization with an April fiscal year start, a YoY measure built and validated in Q2 (calendar terms) looked correct for months, then silently returned a comparison against the wrong period the moment the fiscal year rolled over at the actual boundary — caught only when a finance stakeholder flagged that "this year vs last year" numbers didn't match their fiscal close figures for the transition month.

THE LESSON

Built-in DAX time intelligence functions are calendar-year functions by default, full stop — using them on a fiscal calendar works by coincidence for most of the year and breaks specifically at the boundary where fiscal and calendar logic diverge. Any organization on a non-January fiscal year needs time intelligence measures built explicitly off fiscal columns in the date table, not assumed to work automatically with SAMEPERIODLASTYEAR  or DATEADD.