Model the data · Topic 4 of 6
Time Intelligence and Semi Additive Measures
PL-300 time intelligence: year to date, prior year and rolling period DAX, plus semi additive measures for balances and stock using closing balance functions.
- 4 min
- Time intelligence needs a complete date table, marked, with an active relationship.
- TOTALYTD accumulates. SAMEPERIODLASTYEAR and DATEADD shift. DATESINPERIOD makes a rolling window.
- A fiscal year end is passed as the year end date argument.
What it is
Time intelligence functions compare and accumulate values over periods: year to date, the same period last year, a rolling twelve months. They need a marked date table. A semi additive measure is one that you can add across some dimensions but not across time. Bank balances and stock levels are classic examples: you can add the balances of all accounts today, but you must not add today's balance to yesterday's.
How it works
Core time intelligence functions
All take a column of dates from the date table.
| Function | Returns |
|---|---|
| TOTALYTD | The expression accumulated from the start of the year to the current date |
| TOTALQTD, TOTALMTD | The same for quarter and month |
| DATESYTD | The set of dates from the start of the year, used inside CALCULATE |
| SAMEPERIODLASTYEAR | The same dates one year earlier |
| DATEADD | Shifts dates by a number of intervals: day, month, quarter or year |
| PARALLELPERIOD | A whole parallel period, such as the previous full quarter |
| DATESINPERIOD | A window ending at a date, such as the last 12 months |
| PREVIOUSMONTH, NEXTMONTH and similar | The previous or next period |
Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )
Sales PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales YoY % = DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )
Sales Rolling 12M =
CALCULATE (
[Total Sales],
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH )
)For a fiscal year that ends on June 30, give the year end date as the third argument: TOTALYTD ( [Total Sales], 'Date'[Date], "6/30" ). DATEADD with -1 and YEAR is another way to get the prior year: CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, YEAR ) ).
These functions return a table of dates, which CALCULATE uses as a filter. That is why they sit inside CALCULATE, except for the TOTAL functions that wrap it for you.
Rules that matter
- The date column must come from a date table that is marked and has no gaps, not from the fact table.
- The fact table must have an active relationship to the date table.
- Visuals should use the date table's columns on the axis, not the fact table's date column.
Semi additive measures
An additive measure, such as sales, can be summed across every dimension. A semi additive one cannot be summed over time. If an account holds 100 on Monday and 100 on Tuesday, the total balance is 100, not 200.
Power BI has no built in "last value" aggregation, so you write a measure. The usual patterns are:
| Need | DAX approach |
|---|---|
| Balance at the end of each period | CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR |
| Balance at the start of a period | OPENINGBALANCEMONTH and similar |
| The last date that has data | LASTNONBLANK inside CALCULATE |
| Average level over the period | AVERAGE of the daily value |
Closing Stock =
CALCULATE (
SUM ( Inventory[Quantity] ),
LASTNONBLANK ( 'Date'[Date], CALCULATE ( COUNTROWS ( Inventory ) ) )
)
Month End Balance =
CLOSINGBALANCEMONTH ( SUM ( Balances[Amount] ), 'Date'[Date] )LASTDATE returns the last date in the current filter context, which may have no row, so the result can be blank. LASTNONBLANK finds the last date that does have a value, which is usually what a balance needs. Across other dimensions, such as accounts or warehouses, the measure still adds up normally.
A worked scenario
A warehouse table stores one stock row per product per day. A report shows quarters, and a plain SUM returns three months of stock added together. The analyst writes Closing Stock with LASTNONBLANK so each quarter shows the quantity on its last day with data. She also builds Sales YTD with TOTALYTD, Sales PY with SAMEPERIODLASTYEAR, and a YoY % measure with DIVIDE, all against the marked Date table.
What to remember for the exam
- Time intelligence needs a complete date table, marked, with an active relationship.
- TOTALYTD accumulates. SAMEPERIODLASTYEAR and DATEADD shift. DATESINPERIOD makes a rolling window.
- A fiscal year end is passed as the year end date argument.
- Balances and stock are semi additive. Use closing balance functions or LASTNONBLANK, not SUM.
- LASTNONBLANK finds the last date with data. LASTDATE can return a date with no data.
Using the fact table's date column in time intelligence functions gives wrong or empty results.
How the exam asks about this
- "Show sales from January 1 to the current date." TOTALYTD or DATESYTD.
- "Compare with the same month last year." SAMEPERIODLASTYEAR or DATEADD.
- "Show the stock on the last day of each month." CLOSINGBALANCEMONTH or LASTNONBLANK.
- "Rolling twelve months total." DATESINPERIOD.
Common mistakes
Summing balances over time.
Using LASTDATE when the last date has no row.
Skipping Mark as date table.
Forgetting the fiscal year end argument.
Check yourself
Which function returns the same dates one year earlier?
SAMEPERIODLASTYEAR.
Why is a stock level semi additive?
It can be added across products, but not across time.
Which function finds the last date that has data?
LASTNONBLANK.
Topic 4 of 6 in Model the data
Test yourself on Model the data
The full Microsoft PL-300 question bank has 657 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.