Semi-additive measure

What is a semi-additive measure?

A semi-additive measure is a number that can be summed across some dimensions but not all of them. In practice, the dimension it cannot be summed across is almost always time. Kimball's own definition puts it plainly: semi-additive measures can be summed across some dimensions but not others, and balance amounts are the common case because they are additive across every dimension except time.

The textbook example is a bank balance. If three customers hold 100, 200, and 300 euros, the total held is 600 euros, so balance sums cleanly across customers. But if one account holds 500 euros on Monday and still 500 on Tuesday, the customer does not have 1000 euros. Adding a balance across days double-counts the same money. The measure is additive over customer and non-additive over time, which is what makes it semi-additive.

Additive, semi-additive, and non-additive

Measures in a fact table fall into three groups, and the difference is which dimensions you can add across.

• Additive. Sums across every dimension. Sales revenue and units sold are additive: total them across product, store, and date and the answer always makes sense. These are the easiest measures to work with and the ones you want most of.

• Semi-additive. Sums across some dimensions but not others, typically not time. Account balances, stock on hand, and headcount all behave this way: valid across accounts, products, or departments, wrong when summed across dates.

• Non-additive. Cannot be summed across any dimension. Ratios and percentages such as margin or conversion rate fall here. You cannot add two margins together; you have to store the additive parts, the numerator and denominator, and compute the ratio at the end.

Why balances and inventory behave this way

Semi-additive measures show up whenever a fact table records a level rather than a flow. A sale is a flow: each row is a fresh event, and events add up over time. A balance is a level: each row restates the same standing quantity at a new moment. Restating is not the same as adding.

This is why semi-additive measures live in a snapshot fact table, which captures the state of something at regular intervals: the balance at the close of each day, the stock count at the end of each week. The snapshot grain is what creates the semi-additive behaviour, because summing snapshots over time adds a quantity to itself.

How to aggregate across time

Since summing over time is wrong, you pick a different rule for the time dimension while still summing across the rest. Two rules cover most cases.

The first is to take the value at a point in time, usually the last one in the period. December stock on hand is the closing snapshot of the year, not the sum of twelve monthly snapshots. The second is to average over time, which suits questions like average inventory across a quarter.

In Power BI, this is a common reason to write a measure in DAX rather than lean on the default sum. The pattern combines a normal aggregation across the other dimensions with a time rule such as the last non-empty date in the current period. Functions in the time-intelligence family, such as those that find the closing balance of a period, exist precisely for semi-additive measures.

Closing Stock =
CALCULATE (
    SUM ( Inventory[Units] ),
    LASTNONBLANK ( 'Date'[Date], 1 )
)

The measure still adds units across every product and warehouse. The LASTNONBLANK part restricts the time dimension to the last date that has data, so the period shows the closing level instead of a meaningless running sum.

What to watch out for with semi-additive measures

The default sum is silently wrong. A BI tool will happily add a balance across months and show a total that looks plausible and is nonsense. Nothing errors, so the mistake survives until someone reconciles against the source.

Last is not the only answer. Closing value, opening value, and average each answer a different question. Decide which the report actually wants before writing the measure, because they can differ a lot in a volatile month.

Empty periods trip up closing logic. If a snapshot is missing for the final day, a naive last-date rule returns nothing. This is why the DAX pattern looks for the last date that has data rather than the last date on the calendar.

Mixing measure types in one visual misleads. Putting an additive total next to a semi-additive balance in the same time chart invites readers to compare them as if they aggregate the same way. Label them clearly, or keep them apart.