Skip to content
FR

Time Intelligence, Semi-Additive Measures, and Calculation Groups

Step 11 of 24

Time Intelligence, Semi-Additive Measures, and Calculation Groups

Introduction

This lesson finishes the domain Model the data (25–30% of the PL-300 skills measured, outline dated April 20, 2026). Three skills from the official outline are covered here: creating time intelligence measures (year-to-date, prior year, rolling periods), creating semi-additive measures (balances and snapshots that must not be summed over time) and creating calculation groups (reusable calculation logic applied to many measures). Questions usually show a model and a business requirement ("compare with the same period last year", "show the closing stock for a month", "avoid writing 20 measures") and ask which formula or feature solves it. Microsoft does not publish the question count or the mix of question types; OnLevelUp mock exams use a 50-question practice length and an 80% practice target, neither of which is official or a conversion of the 700/1000 passing score.

After this lesson you will be able to prepare a correct date table for time intelligence, write the main time intelligence patterns, choose between CLOSINGBALANCEMONTH, LASTDATE and LASTNONBLANK for balances, and build a time intelligence calculation group in Tabular Editor or in Power BI Desktop's model view.

Learning objectives

  • Explain what a date table must contain for time intelligence functions to work.
  • Write measures with TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD, DATESINPERIOD and DATESBETWEEN.
  • Calculate year-over-year growth and rolling averages.
  • Explain why balances and stock levels are semi-additive and write measures with CLOSINGBALANCEMONTH, LASTDATE, LASTNONBLANK and CALCULATE.
  • Create a calculation group with calculation items using SELECTEDMEASURE() and use SELECTEDMEASUREFORMATSTRING() for formats.
  • Identify the limits of calculation groups (explicit measures only, precedence, interaction with implicit measures).

1. Prerequisites: a proper date table

Every time intelligence function works on a date column and expects it to be contiguous and complete. Requirements:

  1. A dedicated date table (a dimension) with one row per day, no gaps, covering every year used by the fact tables from 1 January of the first year to 31 December of the last year.
  2. A column of data type Date (or Date/Time with no time part) containing unique values.
  3. The table marked as a date table (Table tools → Mark as date table), selecting the date column. Marking tells Power BI which column to use, disables the automatic date hierarchy for it, and makes functions behave correctly.
  4. A relationship from the fact table's date column to the date table's date column (one-to-many, single direction).
DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2027, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Quarter", "Q" & QUARTER ( [Date] )
)

CALENDAR takes explicit start and end dates; CALENDARAUTO scans the model's date columns and builds full years automatically. Sort Month by Month Number using Sort by column so that months do not appear alphabetically.

Why not use the fact table's own date column? Because the fact table only has rows for days with transactions. A missing date breaks the contiguous range that functions such as SAMEPERIODLASTYEAR need, and prior-period comparisons return wrong or blank results.

Exam tip: if a time intelligence measure returns blank or odd results, check first: is there a date table, is it marked, is it contiguous, and does the visual use the date table's columns (not the fact table's date column) on the axis?

2. Time intelligence functions

Time intelligence functions fall into families. All return either a table of dates (used as a filter inside CALCULATE) or, for the TOTAL... family, a scalar.

Family Examples Typical use
Period-to-date DATESYTD, DATESQTD, DATESMTD, TOTALYTD, TOTALQTD, TOTALMTD Year-to-date sales
Shift in time SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD, PREVIOUSMONTH, NEXTDAY Prior year or prior month comparison
Date range DATESINPERIOD, DATESBETWEEN Rolling 3 months, custom range
First / last FIRSTDATE, LASTDATE, FIRSTNONBLANK, LASTNONBLANK, ENDOFMONTH Balances, openings and closings

Year-to-date

Sales YTD =
TOTALYTD ( [Total Sales], DimDate[Date] )

-- Equivalent, more flexible form
Sales YTD (CALCULATE) =
CALCULATE ( [Total Sales], DATESYTD ( DimDate[Date] ) )

TOTALYTD has an optional third argument, the year-end date, for fiscal years: TOTALYTD ( [Total Sales], DimDate[Date], "30-6" ) for a fiscal year ending 30 June. Prefer the CALCULATE form when you need extra filters, because CALCULATE makes the filter logic visible.

Prior period and growth

Sales PY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( DimDate[Date] ) )

Sales YoY % =
VAR Current = [Total Sales]
VAR Previous = [Sales PY]
RETURN DIVIDE ( Current - Previous, Previous )

SAMEPERIODLASTYEAR shifts the dates currently in filter context back exactly one year. DATEADD is more general: DATEADD ( DimDate[Date], -1, MONTH ) shifts one month; the interval can be DAY, MONTH, QUARTER or YEAR.

Function Shifts Notes
SAMEPERIODLASTYEAR -1 year Equal to DATEADD ( …, -1, YEAR )
DATEADD Any number of intervals Works on a contiguous selection of dates
PARALLELPERIOD Whole periods Returns the entire shifted period, even if the current selection covers only part of it
PREVIOUSMONTH / PREVIOUSQUARTER / PREVIOUSYEAR One period back Whole previous period

DATEADD shifts only the dates present in the current selection (a selection of 1–15 March becomes 1–15 February), while PARALLELPERIOD returns the full previous month. That difference is a favourite exam trap.

Rolling windows

Sales Rolling 3M =
CALCULATE (
    [Total Sales],
    DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -3, MONTH )
)

Avg Monthly Sales Rolling 12M =
AVERAGEX (
    DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH ),
    [Total Sales]
)

Note the second pattern: the average of daily values is not the average of monthly values; if you need a monthly average, iterate over distinct months (for example VALUES ( DimDate[Year Month] )).

DATESBETWEEN ( DimDate[Date], start, end ) returns a custom range, for example from the start of the year to the last day with data: DATESBETWEEN ( DimDate[Date], DATE ( 2026, 1, 1 ), MAX ( FactSales[OrderDate] ) ).

Worked example: year-over-year comparison

Scenario. Contoso Retail has FactSales (OrderDate, Amount) related to DimDate. The CFO wants a table visual with months on rows and three columns: sales, sales in the same month last year, and the percentage change. In January 2027 the visual should show blanks for the prior year if no 2026 data exists, not errors.

Step 1 – confirm the model. DimDate covers 2022–2027, is marked as a date table, and is related to FactSales[OrderDate].

Step 2 – base measure. Total Sales = SUM ( FactSales[Amount] ).

Step 3 – prior-year measure. Sales PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( DimDate[Date] ) ). In the row for March 2027, DimDate[Date] is filtered to 1–31 March 2027; the function returns 1–31 March 2026, and CALCULATE replaces the date filter, so the measure returns March 2026 sales.

Step 4 – growth measure. Use DIVIDE so that an empty or zero prior year returns blank rather than a divide-by-zero error: Sales YoY % = DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] ).

Step 5 – formatting. Format Sales YoY % as percentage in the Measure tools ribbon, and put DimDate[Month] (sorted by month number) on rows, not FactSales[OrderDate].

Why the alternatives are worse. Using FactSales[OrderDate] inside SAMEPERIODLASTYEAR fails because the fact column has gaps and is not the marked date column. Writing [Total Sales] / [Sales PY] yields errors for months without a prior year. Using PARALLELPERIOD would also work for full months but is overkill and returns the whole month when the visual is filtered to part of one.

3. Semi-additive measures

An additive measure (sales amount) can be summed across every dimension. A non-additive measure (a ratio) cannot be summed at all. A semi-additive measure can be summed across some dimensions (store, product, account) but not across time: summing the stock level of every day in a month is meaningless. Examples: inventory on hand, bank balance, headcount, number of open tickets.

The typical source is a snapshot fact table with one row per entity per day (or per month), for example FactInventory (Date, ProductKey, UnitsOnHand). In a monthly visual, the correct value is the balance at the end of the month; at the year level it is the balance on the last day of the year, not the sum of twelve months.

Closing balance patterns

-- Simple: last date in the current filter context that has data for the snapshot
Closing Stock =
CALCULATE (
    SUM ( FactInventory[UnitsOnHand] ),
    LASTDATE ( FactInventory[SnapshotDate] )
)

-- Safer when the last day of a period may have no snapshot
Closing Stock (last non-blank) =
CALCULATE (
    SUM ( FactInventory[UnitsOnHand] ),
    LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactInventory ) ) )
)

-- Built-in shortcuts
Closing Stock EOM = CLOSINGBALANCEMONTH ( SUM ( FactInventory[UnitsOnHand] ), DimDate[Date] )
Closing Stock EOY = CLOSINGBALANCEYEAR ( SUM ( FactInventory[UnitsOnHand] ), DimDate[Date] )
Opening Stock = OPENINGBALANCEMONTH ( SUM ( FactInventory[UnitsOnHand] ), DimDate[Date] )

How they differ:

Function Behaviour When to use
LASTDATE Last date in the current filter context Snapshots exist for every day, or you use the snapshot table's own date
LASTNONBLANK ( dates, expression ) Last date for which the expression is not blank Weekends or missing days without snapshots
CLOSINGBALANCEMONTH/QUARTER/YEAR Value at the last date of the period containing the current dates Quick, readable closing balance
OPENINGBALANCE… Value at the day before the period starts Opening balances

The DAX reference warns that LASTNONBLANK may cause performance and memory problems on large models, so use LASTDATE or a pre-computed "last snapshot" flag column when you can.

Another very common option avoids the semi-additive functions: keep the snapshot on a single Latest basis with a calculated column or filter on a flag such as IsMonthEnd. In Power BI, you can also set the default summarization of a column, but this does not solve semi-additivity; only a measure does.

Worked example: month-end headcount

Scenario. HR stores FactHeadcount (SnapshotDate, DepartmentKey, Employees) with one row per department on the last day of each month. A matrix with months as columns and departments as rows should show employees at the end of each month, and the Total column (the year) should show the figure for December, not the sum of twelve months.

Step 1. A plain SUM ( FactHeadcount[Employees] ) gives 12 × the real value in the year total, because the filter context for the year includes twelve snapshots.

Step 2. Write Employees EOP = CALCULATE ( SUM ( FactHeadcount[Employees] ), LASTDATE ( FactHeadcount[SnapshotDate] ) ). For the month of March, LASTDATE keeps 31 March; for the year, it keeps 31 December of the filtered year (the last snapshot date existing in the context).

Step 3. Add the department rows. The measure stays additive across departments, so the matrix subtotal sums departments for that date correctly.

Step 4. If the company sometimes loads snapshots on the 30th rather than the 31st, LASTDATE still works because it uses the dates present in the fact table. If you used DimDate[Date] there, you would need LASTNONBLANK instead, because the last calendar date has no row.

Why the alternatives are worse. AVERAGE would hide the true closing value. MAX ( Employees ) returns the largest department, not a total. Summing and dividing by 12 gives an average headcount, a different metric.

4. Calculation groups

A calculation group is a table with one column whose rows are calculation items. Each item is a DAX expression that modifies whatever measure is being evaluated, using SELECTEDMEASURE() as a placeholder. They remove the need to write Sales YTD, Margin YTD, Units YTD, Sales PY, and so on, for every base measure: you write the YTD logic once and the user (or visual) applies it to any measure.

-- Calculation group: Time Calculation, column Time Calc
Current:  SELECTEDMEASURE ()
YTD:      CALCULATE ( SELECTEDMEASURE (), DATESYTD ( DimDate[Date] ) )
PY:       CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( DimDate[Date] ) )
YoY %:    DIVIDE ( SELECTEDMEASURE () - CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( DimDate[Date] ) ),
                   CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( DimDate[Date] ) ) )

With this calculation group, a matrix with Time Calc on columns and measures [Total Sales], [Margin] on rows gives all combinations from a handful of definitions.

Creating them

In Power BI Desktop, open Model view and use Calculation group in the ribbon (or the Data pane), then add calculation items in the Properties pane or in the DAX editor. You can also use Tabular Editor. Creating a calculation group in Desktop requires, as of the current product, that you accept to discourage implicit measures (the model setting DiscourageImplicitMeasures is turned on), because calculation groups only apply to explicit measures. Implicit measures, created by dragging a numeric column to a visual, are not modified by calculation items.

Format strings

A percentage item must not show 1,234.00 for a growth rate. Each calculation item has a format string expression:

-- Format string expression for the YoY % item
"0.0%"

-- For items that must keep the base measure format
SELECTEDMEASUREFORMATSTRING ()

Precedence

When a model has several calculation groups (for example Time Calculation and Currency Conversion), the group's Precedence property determines which one is applied first. The group with the higher precedence number is evaluated first, wrapping the others. Wrong precedence gives wrong numbers; it is a recognised trap for questions with two groups.

Limits and tips

  • Calculation groups need explicit measures and a Power BI semantic model at compatibility level 1500 or higher.
  • Row-level security and calculation groups can coexist; calculation groups do not expose data, they change calculations.
  • Sorting items: use the Ordinal property so the items appear in a logical order.
  • Use ISSELECTEDMEASURE ( [Measure1], [Measure2] ) inside an item to apply different logic to specific measures, for example not computing YoY % on a measure that is already a ratio.
  • Visual calculations, a different feature, work inside the visual; calculation groups work in the model for all reports.

Comparison: three ways to deliver "YTD for every measure"

Approach Effort Reuse Weakness
One YTD measure per base measure High None Dozens of near-identical measures
Quick measure (YTD) Low Per measure Generates a measure each time, still duplicated
Calculation group Medium (once) All explicit measures Needs explicit measures, precedence management

5. Common pitfalls and exam tips

  • No marked date table: blank prior-year or YTD results. Create the date table, mark it, and relate it to the fact.
  • Using the fact date column on the axis: time intelligence breaks. Use the date table's columns.
  • Auto date/time: the hidden date tables created automatically work, but they increase model size and are discouraged in favour of one shared date table.
  • DATEADD vs PARALLELPERIOD: partial selection versus whole period.
  • Summing a balance over time: use a closing balance pattern.
  • LASTDATE vs LASTNONBLANK: the first uses calendar dates in context; the second skips dates without data.
  • Implicit measures with calculation groups: the group has no effect on them.
  • Role-playing dates: to compute time intelligence on the ship date, use USERELATIONSHIP inside CALCULATE, or a second date table.
  • Exam tip: when the question says "avoid creating many similar measures", think calculation group; when it says "closing balance", think CLOSINGBALANCE…, LASTDATE or LASTNONBLANK.

6. Mini case study

Situation. Fabrikam Logistics stores daily parcel volumes in FactParcels (Date, DepotKey, Parcels) and a weekly inventory of vans in FactVans (WeekEnding, DepotKey, VansInService). Reports need: (1) parcels year-to-date and the same figure last year; (2) the number of vans in service at the end of each month; (3) both YTD and prior-year views for four measures without creating eight extra measures. A DimDate table exists but is not marked as a date table, and one report page shows blanks for last year.

Question. What do you change, and which measures do you write?

Solution, commented.

  1. Mark DimDate as a date table and verify it has contiguous dates, and that the relationships connect FactParcels[Date] and FactVans[WeekEnding] to DimDate[Date]. The blank prior-year page is explained by the missing mark or by the report using the fact's date column.
  2. Parcels YTD with CALCULATE ( [Total Parcels], DATESYTD ( DimDate[Date] ) ) and Parcels PY with SAMEPERIODLASTYEAR; no need for separate measures if step 4 is done.
  3. Vans at month-end is semi-additive: Vans EOM = CALCULATE ( SUM ( FactVans[VansInService] ), LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactVans ) ) ) ). Weekly snapshots mean the last calendar day of a month usually has no row, so LASTDATE on DimDate would return blank; LASTNONBLANK finds the last week-ending in the month.
  4. Calculation group Time Calculation with items Current, YTD and PY, applied to the four explicit base measures. Turn off implicit measures. Format items with SELECTEDMEASUREFORMATSTRING().

Why not the alternatives. Summing VansInService across weeks would count the same vans four or five times. Creating eight additional measures works but is error-prone to maintain. A quick measure for each would still produce eight objects.

Key terms

  • Date table: contiguous dimension with one row per day, marked as date table.
  • Time intelligence: DAX functions that shift or expand date filters.
  • YTD / QTD / MTD: period-to-date calculations.
  • Semi-additive measure: can be summed over some dimensions but not over time.
  • Snapshot fact table: table of periodic states (balances, stock).
  • Calculation group: table of calculation items modifying measures.
  • Calculation item: DAX expression using SELECTEDMEASURE().
  • Precedence: order of evaluation between calculation groups.
  • Explicit measure: measure created with DAX, required by calculation groups.

Summary checklist

  • I can build and mark a contiguous date table and relate it to the facts.
  • I can write YTD, prior-year, growth and rolling-window measures.
  • I know the difference between DATEADD and PARALLELPERIOD.
  • I can explain why balances are semi-additive and write a closing-balance measure.
  • I can choose between LASTDATE, LASTNONBLANK and CLOSINGBALANCE….
  • I can create a calculation group with SELECTEDMEASURE() and set format strings and precedence.
  • I know calculation groups require explicit measures.

Official sources