2.6 — Time Intelligence and Semi-Additive Measures
PL-300 Domain 2 · Skill 2.6
What You Need to Know
Time intelligence functions shift or extend the dates in the filter context. They need a proper date table (lesson 2.3): contiguous, related to the fact table, and marked as a date table.
Period-to-date
| Measure | DAX |
|---|---|
| Year to date | Sales YTD = TOTALYTD([Total Sales], 'Date'[Date]) or CALCULATE([Total Sales], DATESYTD('Date'[Date])) |
| Fiscal YTD (year ends 30 June) | TOTALYTD([Total Sales], 'Date'[Date], "6/30") |
| Quarter / month to date | TOTALQTD, TOTALMTD, or DATESQTD, DATESMTD |
Comparing periods
| Function | Returns | Example |
|---|---|---|
SAMEPERIODLASTYEAR(dates) |
The same dates one year earlier | Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) |
DATEADD(dates, n, interval) |
Dates shifted by n days, months, quarters or years | Previous month: DATEADD('Date'[Date], -1, MONTH) |
PARALLELPERIOD(dates, n, interval) |
The whole shifted period | Full previous quarter, even when one month is selected |
PREVIOUSMONTH, PREVIOUSYEAR, NEXTMONTH… |
The whole previous or next period | |
DATESINPERIOD(dates, end, n, interval) |
A rolling window | Last 12 months: DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH) |
DATESBETWEEN(dates, start, end) |
A custom range |
Year-over-year growth:
YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])
Semi-additive measures
An additive measure can be summed over every dimension (sales amount). A semi-additive measure can be summed over some dimensions but not over time: inventory on hand, account balances, headcount. Adding 12 month-end balances gives a meaningless number. You want the closing balance of the period.
| Need | DAX |
|---|---|
| Balance on the last date of the period | CALCULATE(SUM(Stock[Qty]), LASTDATE('Date'[Date])) |
| Last date that has data (handles weekends and future dates) | CALCULATE(SUM(Stock[Qty]), LASTNONBLANK('Date'[Date], CALCULATE(SUM(Stock[Qty])))) |
| Month-end, quarter-end, year-end | CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR |
| Opening balance | OPENINGBALANCEMONTH and similar, or FIRSTDATE / FIRSTNONBLANK |
| Average balance over the period | AVERAGEX(VALUES('Date'[Date]), [Balance]) |
Across products or warehouses the balance still adds up; only time needs special handling.
Key Concept: Time intelligence = CALCULATE plus a function that returns shifted or extended dates from a marked date table. Balances are semi-additive: take the last (non-blank) date in the period instead of summing over time.
Exam Traps
- Using time intelligence without a contiguous, marked date table
- Using fact-table dates (
Sales[OrderDate]) as the dates argument instead of the date table's column - Summing inventory snapshots across months
- Choosing LASTDATE when the last day of the month has no snapshot (use LASTNONBLANK)
- Forgetting the year-end argument for a fiscal year that doesn't end on 31 December
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 22 PL-300 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Time intelligence functions (DAX), Microsoft Learn
- TOTALYTD function (DAX), Microsoft Learn
- SAMEPERIODLASTYEAR function (DAX), Microsoft Learn
- LASTNONBLANK function (DAX), Microsoft Learn