Time Intelligence in DAX, Made Simple

Why "Last Year" Is Harder Than It Looks

Every business dashboard needs time comparisons: this month vs last month, year-to-date vs last year, quarter over quarter. In Excel you'd write SUMIFS with date ranges. In DAX, time intelligence functions handle these elegantly — but only if you've set up the foundation correctly. Almost every time-intelligence bug traces back to a missing date table. So we start there.

Step 1: Build a Proper Date Table

Time intelligence requires a continuous date table with every date, covering your data range (plus enough margin for comparisons like "same period last year"). Create it in Power Query (a list of dates), mark it as the Date table (Table Tools → Mark as Date Table), and build a relationship to your fact table's date column. This single setup unlocks every function below. Without it, functions like TOTALYTD can return wrong or blank results.

Step 2: The Running Total — TOTALYTD

TOTALYTD(expression, date_column) computes a running total from the start of the year to the latest date in context. Sales YTD = TOTALYTD([Total Sales], 'Date'[Date]). Add a filter for the fiscal year end if yours differs: TOTALYTD([Total Sales], 'Date'[Date], "03/31"). TOTALQTD and TOTALMTD follow the same pattern for quarter and month.

Step 3: Comparing to Last Year

SAMEPERIODLASTYEAR returns the dates one year earlier, and combined with CALCULATE it produces the classic comparison: Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])). Then YoY Growth = DIVIDE([Total Sales] – [Sales LY], [Sales LY]). That one measure pattern — current vs same-period-last-year, divided for growth — powers half the KPIs in corporate India.

Step 4: Month Over Month and Other Periods

PREVIOUSMONTH, PREVIOUSQUARTER and PREVIOUSYEAR shift to the immediately preceding period, perfect for MoM and QoQ. DATEADD is the general-purpose shifter: CALCULATE([Total Sales], DATEADD('Date'[Date], -12, MONTH)) achieves the same as SAMEPERIODLASTYEAR, and it generalizes to arbitrary shifts (6 months ago, 30 days ago) that named functions can't do.

Step 5: Moving Averages (Trend Without Noise)

Moving averages smooth volatility and are a favourite in trend analysis. A 90-day moving average uses DATESINPERIOD: CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -90, DAY)) / 90 — or better, AVERAGEX(DATESINPERIOD(…), [Total Sales]). Understanding DATESINPERIOD unlocks rolling windows for any metric.

Step 6: Period-to-Date Partitions

For "same period last year to date" (SPLYTD), combine the two ideas: CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DATESYTD('Date'[Date]))). This shows the comparison so far this year vs the same days last year — the number every quarterly review opens with. Once you can read this nested pattern, you've graduated from time-intelligence basics.

The Common Pitfalls

Three traps catch everyone at some point. Missing date table (everything returns wrong numbers). Not marking the date table (some functions fail). And forgetting that time intelligence uses the filter context — if a slicer filters a different date column, results look broken. Debug by isolating: add a simple table visual with [Total Sales], [Sales YTD] and a date slicer, and watch how each measure responds.

Time intelligence turns a dashboard from "what happened" into "what changed and by how much". That's where the decisions happen.

The Date Table Pattern, Revisited

Because so many time-intelligence failures trace back to this foundation, it's worth repeating the pattern precisely. A good date table is continuous — every calendar date in the range, no gaps — and contains columns for year, quarter, month name, month number, week number and day of week. You can generate it in Power Query with a simple list of dates, or use DAX's CALENDAR function in a calculated table. Whichever you choose, then mark it as the date table under Table Tools, and create a single, correct relationship to your fact table's date column.

Two details beginners miss: the relationship should run from the date table to the fact table (one-to-many, filtering the fact), and you should never create multiple active relationships to the same date column. Keep the model clean, and every time-intelligence function will behave predictably. When a "last year" measure returns blanks, nine times out of ten the fix is in this foundation, not in the DAX.

A Practice Sequence That Cements the Concepts

• Build the date table from scratch in Power Query, then mark it and connect it.
• Create TOTALYTD, TOTALQTD and TOTALMTD on the same measure and watch how the running totals change by page filter.
• Build the YoY growth measure with SAMEPERIODLASTYEAR and verify it against a known figure.
• Recreate the same YoY using DATEADD with -12 MONTH and confirm identical results.
• Add a 90-day rolling average and confirm it smooths a noisy daily series.
• Break your date table (remove a year, unmark it) and observe exactly which measures fail — then fix it back.

That last exercise — deliberately breaking your own setup — is the fastest teacher. Once you've watched a measure fail because the date table wasn't marked, or because a relationship was pointing the wrong way, you'll never debug those cases in a job interview. Time intelligence is one of the highest-frequency topics in real dashboards and in analyst interviews, and the analysts who master it build the KPIs every executive review opens with.

A final tip on wording that interviews reward: describe time intelligence in business terms, not just function names. Say "this measures year-to-date sales against the same period last year, accounting for fiscal year end", rather than reciting TOTALYTD with a filter. Interviewers are hiring an analyst who can translate between numbers and business, and the candidate who frames the KPI in context — while clearly knowing the functions behind it — reads as senior far faster than the candidate who can name thirty functions.

Master the date table, drill the six functions, and translate every measure into the business question it answers. That combination makes time intelligence the least mysterious — and most valuable — part of your DAX toolkit.

And the fastest way to internalize it all is to build one real report with a proper date table and every comparison KPI: YTD, last year, YoY growth, MoM, and a 90-day trend. Debug each until it matches a hand-checked number. When that single report works, you haven't learned five functions — you've learned the whole discipline, and every future dashboard borrows from it.

Build it on a dataset you know well, and you'll instantly trust — and be able to defend — every time measure you ever write after.

When your dates are clean, your comparisons correct, and your measures explainable in one sentence, you've graduated.

Our Top Power BI Course in India

Looking for Power BI training in a specific city? Explore our city-wise Power BI Course pages to find course information, training details, curriculum, practical learning approach, and career guidance for your location.