Time Intelligence in DAX: YTD, Prior Year, and the Variance Pitfalls | Excel & Power BI S2 Ep5

📂 Download the AtlasParts dataset and the Episode 11 Power BI starter file: https://github.com/GoCelesteAI/excel-powerbi-for-finance Episode 5 of Season 2 of *Excel & Power BI for Finance* — time intelligence in DAX. Episode 10 left us with a handful of working measures on the AtlasParts star. This episode answers the questions every finance team gets asked first thing every Monday: revenue year to date, how does it compare to prior year, are we trending up or down, which months bombed and which crushed. Once the calendar Table is in place and marked as Date Table, most of these questions collapse to one or two lines of DAX. We walk through TOTALYTD for year-to-date with optional fiscal year-end. SAMEPERIODLASTYEAR for the classic two-line YTD vs Prior-Year YTD chart. DATEADD for the general shift, including last month for month-over-month variance. The DIVIDE guard for percent variance. And the four pitfalls that break the first version of every time-intelligence measure. What You'll Learn: - Why the calendar Table you built in Episode 9 is load-bearing for time intelligence — every TI function expects a contiguous date range with one row per date, and the journal's posting date won't do. - TOTALYTD — the most-used time-intelligence function in finance. Two arguments for calendar year, three for fiscal year. Drop on a card next to a date slicer and watch it react. - SAMEPERIODLASTYEAR + CALCULATE — the canonical two-line YTD chart. This year vs last year, growing through the year. The pattern every finance dashboard runs. - DATEADD as the general shift — last quarter, last month, two weeks ago. Walks the calendar so January's previous month lands correctly on last year's December. - Month-over-month variance with DIVIDE-guard — the three measures (PM, MoM, MoM %) that drive every KPI card with a coloured arrow. - Four pitfalls — Mark as Date Table, fact's date column, slicing the wrong column, and divide-by-zero. Each of these breaks dashboards in production; each has a one-line fix. Timestamps: 0:00 - Intro — Time intelligence in DAX 0:48 - Five questions to land today 1:37 - Why the calendar Table mattered 3:20 - TOTALYTD — year to date with fiscal year support 5:10 - SAMEPERIODLASTYEAR — prior year on a two-line chart 7:04 - Month-over-month variance with DATEADD 8:46 - DIVIDE — the variance-percent gotcha 9:59 - The four pitfalls 11:30 - Recap — five working measures 12:39 - Up next — Finance Dashboard end-to-end Key Takeaways: 1. Mark as Date Table is load-bearing. SAMEPERIODLASTYEAR and DATEADD silently return wrong numbers if the calendar Table isn't marked. Modeling → Mark as Date Table → choose Date column. Once, immediately after creating the calendar. 2. Always slice through the calendar's Date column, never through the fact's posting date. TOTALYTD respects the calendar's filter via the relationship to the fact. Slice through the fact directly and TOTALYTD ignores the slicer. 3. TOTALYTD takes an optional third argument for fiscal year-end as a "MM-DD" string. That single argument is the difference between a measure that's right for accounting and a measure that's right for nobody. Don't ship a fiscal-year company on calendar-year measures. 4. SAMEPERIODLASTYEAR is the cleaner one-liner for year-over-year. DATEADD is what you reach for when you need a non-yearly shift. Both walk the calendar so year-boundary cases (January's previous month is last year's December) work automatically — no manual offset arithmetic, no off-by-one zeros. 5. Always use DIVIDE for percent variance, never the slash. Some prior period genuinely will be zero. The slash returns Infinity which renders as a dash in the visual; DIVIDE with a third argument fallback (usually 0) keeps the dashboard valid. #PowerBI #DAX #TimeIntelligence #YTD #FinanceAnalytics #PowerBIDesktop #ExcelToPowerBI #FinancialReporting #BusinessIntelligence --- Generated by GoCelesteAI · part of the Excel & Power BI for Finance series