Semi-additive measures in DAX: balances and closing values

A semi-additive measure can be summed across some dimensions but not across time. Stock levels, account balances and headcount are the classic examples: adding product balances across categories is meaningful, but adding Monday’s stock level to Tuesday’s is not.

Additive, semi-additive and non-additive

Picking the right date

Snapshot tables store one row per entity per date. The measure must still use an aggregation function (a measure can’t reference a column directly), but it filters to a single date, so the SUM only adds rows for that one date.

Stock on Hand =
CALCULATE (
    SUM ( Inventory[UnitsBalance] ),
    LASTDATE ( 'Date'[Date] )
)

LASTDATE returns the last date in the current filter context. If no snapshot exists for that date (it is in the future, or snapshots skip weekends), the measure returns blank for the month and for the total. LASTNONBLANK fixes this: it iterates the dates in context from latest to earliest and returns the last date for which the expression isn’t blank.

Stock on Hand =
CALCULATE (
    SUM ( Inventory[UnitsBalance] ),
    LASTNONBLANK (
        'Date'[Date],
        CALCULATE ( SUM ( Inventory[UnitsBalance] ) )
    )
)

The inner CALCULATE matters: LASTNONBLANK evaluates its expression in row context, and CALCULATE performs the context transition to filter context so the sum is evaluated for each date. FIRSTNONBLANK works the same way in ascending order.

Opening and closing balance functions

Month End Inventory Value =
CLOSINGBALANCEMONTH (
    SUMX ( Inventory, Inventory[UnitCost] * Inventory[UnitsBalance] ),
    'Date'[Date]
)

Each balance function accepts an optional filter and either a date column or a calendar reference. These functions aren’t supported in DirectQuery mode when used in calculated columns or RLS rules, a restriction shared by the other time intelligence functions.

Finally, hide the raw balance column (UnitsBalance in the example) so report authors can’t drag it into a visual and get a meaningless default sum; the semi-additive measure becomes the only way to show the balance.

Common trap: Leaving a balance column visible with its default Sum summarisation - adding daily snapshots across dates produces inflated, meaningless totals; hide the column and expose a semi-additive measure instead.

Common trap: Using LASTDATE when the last day of the period has no snapshot - LASTDATE returns the last date in context even if no data exists for it, so the result is blank; LASTNONBLANK finds the last date that actually has a value.

Common trap: Assuming OPENINGBALANCEMONTH evaluates on the first day of the month - the function reference states it evaluates at the date corresponding to the end of the previous month, which is the closing balance of the prior month.

Get the whole book

This note is one section of Ultra Transcenders PL-300: Microsoft Power BI Data Analyst, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.

About the book · PL-300 terms in the glossary · All PL-300 study notes