Time Intelligence & Calculation Groups
Master time intelligence DAX functions — year-to-date, same period last year, semi-additive measures — and learn how calculation groups reduce measure duplication.
Comparing across time
Think about your bank account. You don’t just want to know your balance today — you want to know if it’s higher than last month, how much you’ve spent year-to-date, and whether you’re on track compared to the same time last year.
Time intelligence DAX functions let you ask these same questions about your business data: year-to-date revenue, this quarter vs last quarter, running totals over months. They all require a proper date table (covered in the Date Tables module).
Essential time intelligence functions
Year-to-date
YTD Revenue =
TOTALYTD(
SUM(Sales[Revenue]),
'Date'[Date]
)Or equivalently using CALCULATE + DATESYTD:
YTD Revenue =
CALCULATE(
SUM(Sales[Revenue]),
DATESYTD('Date'[Date])
)Riley at Coastal Fresh (🛒) puts this in a matrix with months as rows. In March, it shows Jan + Feb + Mar cumulative revenue. In June, it shows Jan through June.
Same period last year
Revenue Last Year =
CALCULATE(
SUM(Sales[Revenue]),
SAMEPERIODLASTYEAR('Date'[Date])
)YoY Change % =
VAR Current = SUM(Sales[Revenue])
VAR LastYear = CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
DIVIDE(Current - LastYear, LastYear)Kenji at Apex Manufacturing (🏭) uses YoY Change to compare this April’s production to last April’s.
DATEADD: flexible period shifting
Revenue 3 Months Ago =
CALCULATE(
SUM(Sales[Revenue]),
DATEADD('Date'[Date], -3, MONTH)
)DATEADD is more flexible than SAMEPERIODLASTYEAR — you can shift by any number of days, months, quarters, or years.
Common time intelligence functions
| Function | What It Does |
|---|---|
TOTALYTD(measure, date) | Year-to-date accumulation |
TOTALQTD(measure, date) | Quarter-to-date |
TOTALMTD(measure, date) | Month-to-date |
SAMEPERIODLASTYEAR(date) | Shifts dates back one year |
DATEADD(date, intervals, type) | Shifts dates by any amount |
PREVIOUSMONTH(date) | Returns dates from the previous month |
PREVIOUSQUARTER(date) | Returns dates from the previous quarter |
DATESYTD(date) | Returns year-to-date dates |
DATESINPERIOD(date, start, intervals, type) | Returns a custom rolling window |
Exam tip: time intelligence requirements
Time intelligence functions work best with:
- A marked date table (Table tools → Mark as date table)
- Contiguous dates — no gaps in the date column
- The date column should be Date or Date/Time data type
If time intelligence returns unexpected results, check these requirements first. Power BI’s auto date/time feature provides basic time intelligence without a custom date table, but a dedicated date table gives you full control and is the exam-expected approach.
Semi-additive measures
Some values shouldn’t be summed across time. Kenji tracks inventory levels — if he has 500 units on Monday, 480 on Tuesday, and 510 on Wednesday, the weekly inventory isn’t 1,490 (the sum). It’s 510 (the latest value).
Current Inventory =
CALCULATE(
SUM(Inventory[Quantity]),
LASTDATE('Date'[Date])
)Or using CLOSINGBALANCEMONTH for month-end snapshots:
Month End Balance =
CLOSINGBALANCEMONTH(
SUM(Accounts[Balance]),
'Date'[Date]
)Semi-additive examples: inventory counts, account balances, headcount, stock prices — anything where summing across time gives a meaningless number.
| Measure Type | Sum Across Time? | Example | DAX Approach |
|---|---|---|---|
| Additive | Yes ✓ | Revenue, units sold, hours worked | SUM() works fine |
| Semi-additive | No ✗ | Inventory, headcount, account balance | Use LASTDATE, CLOSINGBALANCEMONTH, or LASTNONBLANK |
| Non-additive | No ✗ | Ratios, percentages, averages | Calculate from additive components |
Calculation groups
Calculation groups let you apply time intelligence (or any transformation) to multiple measures without duplicating code.
Without calculation groups, Nadia at Prism Agency (📊) would need:
- Revenue YTD, Clicks YTD, Impressions YTD, Spend YTD
- Revenue LY, Clicks LY, Impressions LY, Spend LY
- Revenue YoY%, Clicks YoY%, Impressions YoY%, Spend YoY%
That’s 12 measures for 4 base measures × 3 time views. With 10 base measures, it becomes 30 measures.
Calculation groups define the time transformation ONCE:
| Calculation Item | Logic |
|---|---|
| Current | SELECTEDMEASURE() (no change) |
| YTD | CALCULATE(SELECTEDMEASURE(), DATESYTD(‘Date’[Date])) |
| Prior Year | CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(‘Date’[Date])) |
| YoY % | (Current - Prior Year) / Prior Year |
Now any measure can use these items: drop the calculation group on a visual, and every measure gets YTD, Prior Year, and YoY% automatically.
How to create calculation groups
Calculation groups are created in Model view → New calculation group (or using external tools like Tabular Editor).
Each calculation group:
- Has a name (e.g., “Time Intelligence”)
- Contains calculation items (e.g., “YTD”, “Prior Year”)
- Uses
SELECTEDMEASURE()to reference whatever measure is being modified - Appears as a column in the Fields pane that can be placed on visuals
On the exam, know that calculation groups reduce measure proliferation — one group with 4 items applied to 10 base measures replaces 40 individual measures.
Knowledge check
Kenji tracks factory inventory. He uses SUM(Inventory[Quantity]) and sees that the monthly total is 15,000 — but the actual inventory is 500 units. What went wrong?
Which DAX function shifts dates by a flexible number of periods (days, months, quarters, or years)?
🎬 Video coming soon
Next up: Model Performance Optimisation — find and fix slow queries with Performance Analyzer and DAX query view.