Launch Deal · 50% off 1st month ·

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.

See plans

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