Excel & Power BI for Finance: Time Intelligence in DAX: YTD, Prior Year, and the Variance Pitfalls

Download the AtlasParts dataset and the Episode 11 Power BI starter file — follow along in your own copy of Power BI Desktop: github.com/GoCelesteAI/excel-powerbi-for-finance.

We have measures. We have a star schema. We have a calendar Table marked as the date dimension. Now we can finally answer the questions every finance team gets asked first thing every Monday.

What’s revenue year to date? How does that compare to prior year? Are we trending up or down? Which months bombed and which crushed? These are time-intelligence questions. DAX has a dedicated family of functions for them, and once the calendar Table is in place, most of them collapse to one or two lines of code.

This episode is about getting time intelligence right. Year-to-date measures using TOTALYTD. Prior-year comparisons with SAMEPERIODLASTYEAR. Month-over-month and percent variances that survive the start of the year (when last month was December last year). And the one quiet pitfall that breaks every time-intelligence measure if you forget it: Mark as Date Table.

Why the calendar Table mattered

Episode 9 ended with a small ceremony: we marked our calendar Table as the date dimension. At the time it looked optional. It isn’t.

Every time-intelligence function in DAX assumes there’s a single, unambiguous date column to operate on, with one row per date in the reporting range — including dates with no transactions. TOTALYTD, SAMEPERIODLASTYEAR, DATESYTD, DATEADD, PARALLELPERIOD — all of them quietly walk up the model from your fact Table to the date dimension, ask “where’s the canonical Date column”, and use it.

If you skip Mark as Date Table, the time intelligence functions either error out or silently return wrong numbers. The same is true if you try to do time intelligence using gl_journal[posting_date] directly — slow days, weekends, and holidays will be missing from the date range, and TOTALYTD will report a partial year-to-date. Always go through the calendar.

This is the moment in the model build where Episode 9’s foundation pays off. The work from this episode forward is short — usually one line per measure — because the calendar carries the structural weight.

Year-to-date with TOTALYTD

TOTALYTD is the most-used time-intelligence function in finance. It computes a running total from the start of the fiscal year up to the current date in context.

Revenue YTD =
TOTALYTD([Total Revenue], Calendar[Date])

That’s the entire measure. Two arguments: the measure to total, and the date column in the calendar. Drop it on a card next to a date slicer and watch it work — pick any date and the card shows revenue from January first up to that date.

The natural follow-on is a fiscal year. Most finance teams don’t run on a calendar year — fiscal years can start in April, July, October, or anywhere their treasurer felt like in 1987. TOTALYTD takes an optional third argument for the fiscal year-end:

Revenue YTD (Fiscal) =
TOTALYTD([Total Revenue], Calendar[Date], "06-30")

The string is the last day of the fiscal year as month-dash-day. June thirtieth, here. That single argument is the difference between a measure that’s right for accounting and a measure that’s right for nobody.

Variants exist for quarter and month (TOTALQTD, TOTALMTD) — same shape, different period. Year-to-date is the one that matters most.

Prior year with SAMEPERIODLASTYEAR

A YTD number is interesting. It’s twice as interesting next to last year’s YTD. SAMEPERIODLASTYEAR shifts whatever date range is currently in context backward by exactly one year, then evaluates a measure under that shifted context.

Revenue YTD PY =
CALCULATE(
    [Revenue YTD],
    SAMEPERIODLASTYEAR(Calendar[Date])
)

Wrap the YTD measure in CALCULATE; pass SAMEPERIODLASTYEAR as the filter; done. The filter takes the dates currently visible (say, January through September of this year), shifts them back twelve months (January through September last year), and evaluates the YTD measure under that.

Drop both Revenue YTD and Revenue YTD PY on a line chart with the calendar’s month on the x-axis. You get the classic two-line chart that anchors every finance dashboard: this year and last year side by side, growing through the year.

A more general version uses DATEADD, which lets you shift by any interval:

Revenue YTD PY =
CALCULATE([Revenue YTD], DATEADD(Calendar[Date], -1, YEAR))

Same result. DATEADD is what you reach for when you need a non-yearly shift — last quarter, last month, two weeks ago. SAMEPERIODLASTYEAR is the cleaner one-liner for the most common case.

Month-over-month variance

Year-to-date and prior-year answer the strategic questions. Month-over-month answers the tactical one: did this month do better than last month?

The pattern is two measures. The current month’s revenue (which is just [Total Revenue] in a monthly visual). And a measure that returns last month’s value:

Revenue PM =
CALCULATE([Total Revenue], DATEADD(Calendar[Date], -1, MONTH))

Then variance is straightforward arithmetic:

Revenue MoM Variance =
[Total Revenue] - [Revenue PM]

And percent variance, with the divide-by-zero guard from Episode 10:

Revenue MoM Variance % =
DIVIDE([Total Revenue] - [Revenue PM], [Revenue PM], 0)

Drop Revenue MoM Variance % on a KPI card. Configure the conditional formatting so positive variances are green and negative are red. Now there’s a card on the dashboard whose colour tells you the answer before you read the number.

What about the start of the year?

The first thing that breaks anyone’s first time-intelligence measure is the start of the year. January’s “previous month” is December — but December of last year. If you implement Revenue PM with a manual date offset, January will return zero (or worse, the wrong year’s December).

DATEADD handles this correctly. It walks the calendar Table back one month using the actual dates, so January’s offset lands on the previous December — last year’s December — automatically. SAMEPERIODLASTYEAR is the same: it uses the calendar relationships, not arithmetic.

The reason both work is that the calendar Table has every date, including the silent transitions across year boundaries. This is the third payoff of having a real calendar dim instead of relying on the fact Table’s date column.

Common pitfalls

Three things break most often when finance teams first try time intelligence:

Forgetting Mark as Date Table. SAMEPERIODLASTYEAR and DATEADD will silently return wrong numbers if the calendar isn’t marked. The fix is in Modeling → Mark as Date Table → choose the Date column. Do this once, immediately after creating the calendar.

Using the fact’s date column. gl_journal[posting_date] skips the dates with no postings. Every time-intelligence function expects a contiguous date range. Always pass Calendar[Date], never the fact’s date.

Mixing YTD with a non-date filter. TOTALYTD respects the date filter from the calendar. If your date slicer is on the fact Table’s posting date instead of the calendar’s date, the YTD measure ignores it. Slice through the calendar; everything else flows.

A fourth, subtler pitfall: when you divide by a previous-period value to compute percent variance, that previous value can be zero. DIVIDE with a third argument of zero (or null, depending on what you want the visual to show) is the one-line guard. Never use the division operator directly in a time-intelligence percent measure — sooner or later you’ll trip on a quarter where the prior quarter genuinely was zero.

What we did, what’s next

You should now have five working measures: Revenue YTD, Revenue YTD PY (using either SAMEPERIODLASTYEAR or DATEADD), Revenue PM, Revenue MoM Variance, and Revenue MoM Variance %. Drop them on KPI cards, drop them on a line chart with month on the x-axis, watch them respond as the date slicer moves.

This is the language finance dashboards are built in. Most boards you see in production are some combination of YTD, prior-year YTD, monthly variance, and a percent. The DAX in this episode is most of what’s needed.

Next episode is the season finale — Finance Dashboard end-to-end. We take the AtlasParts star, the DAX measures from Episodes 10 and 11, and assemble the actual board pack: revenue trend, gross margin by region, AR aging, budget versus actual, all on one page. We publish it to the Power BI Service and the dashboard refreshes nightly.

Time intelligence is half of what makes a finance dashboard feel like a finance dashboard. The other half is layout — and that’s Episode 12.