SAC aggregation exception on the time dimension: fixing wrong stock and balance totals

You build a SAC model, drop your stock level into a chart, and the yearly total reads twelve times too high. Nothing is broken — SAC is simply summing a balance across every month, which is the wrong operation for that kind of measure. The fix is a one-time setting called the aggregation exception. Here is exactly what it is and how to apply it.

Why the total is wrong

By default SAC aggregates every measure with SUM across every dimension, including time. That is correct for flows — revenue, units sold, costs add up month by month. But a stock is a snapshot: inventory on hand, cash balance, active subscribers, headcount, a closing loan balance. Adding January's stock to February's and so on produces a meaningless figure. The yearly value of a stock is its end-of-period value, not the sum of the months.

What an aggregation exception is

An aggregation exception tells SAC to keep the normal aggregation (SUM) across business dimensions — region, product, entity — but switch to a different operation on one specific dimension, almost always time. For a stock, that operation is LAST: take the value of the most recent period in the displayed range. The measure still sums correctly across regions and products; it just stops summing across months.

Step by step in the Modeler

Open the model in the Modeler and select the measure. In its properties, find the default aggregation (set to SUM) and add an exception aggregation. Choose the Date (time) dimension as the exception dimension and set the type to LAST. Save the model. From now on, any Story that uses this measure shows the end-of-period value over time while still totalling properly across other axes — no formula, no workaround in the Story.

Which measures need it

Apply LAST on time to: inventory and stock levels, cash and treasury balances, closing loan or deposit balances, number of active customers or subscribers, headcount, and any opening/closing balance. Flows — sales, production, costs, new contracts, tickets opened — keep the default SUM. When in doubt, ask whether summing the measure across twelve months produces a number anyone would quote; if not, it needs the exception.

FIRST, LAST and averages

LAST returns the latest period's value; FIRST returns the earliest (useful for an opening balance). For a measure that is genuinely an average over the period — an average rate or a percentage — use AVERAGE rather than LAST, or better, recompute it from its components. The broader rules for choosing between SUM, AVERAGE, LAST and COUNT are covered in our guide to choosing the right aggregation.

Verify it in a Story

After saving, drop the measure into a table with the time dimension expanded to months plus a yearly total. The yearly total should now equal December's value, not the sum of all months. If it still sums, re-open the measure and confirm the exception is set on the time dimension specifically, not on a different axis.

Inherit it instead of fixing it

Every model in our catalogue ships its measures with the correct aggregation already set — stocks on LAST, flows on SUM, rates on AVERAGE — so you never debug a wrong total. Browse the catalogue or compose your own with the generator, and the exceptions come pre-wired.