Inventory Snapshots
A snapshot table records a balance, not a movement, so it adds up across parts and plants but not across time. On the demo data for 2025 there are five common ways to aggregate it. Two of them are right.
| Method | Value | Verdict | Why |
|---|---|---|---|
| Sum of every month end | $79.02M | Wrong | Twelve balances added together, 9.2 times the real stock. |
| Closing balance, total | $8.63M | Right | Stock on Dec 31, 2025, the last snapshot of the year. |
| LASTNONBLANK, summed by part | $8.81M | Wrong | 18 parts not stocked at year end still show their last balance, $182K in all. The grand total row still shows the right figure, so the rows don't add up to it. |
| Average of the 12 month ends | $6.58M | Right | The basis for inventory turns and days on hand. |
| AVERAGEX by part, skipping empty months | $7.00M | Wrong | Overstates average stock by 6.3%, so turns and days on hand look worse than they are. |
The Trap That Passes Review
Summing snapshots is easy to catch: the number is absurd. LASTNONBLANK is the one that ships. On a card it returns the
year-end balance, because at the total level the last date with any value is Dec 31, 2025. In a table by part, each row evaluates on
its own, and a part last stocked in an earlier month returns that month's balance (14 of the 18 stale parts in this data were last stocked in November). The rows then add to $8.81M under a total of
$8.63M, and nobody can say which number is wrong.
The fix is to find the last snapshot date once, with every filter but the date cleared, and evaluate every row on that date.
REMOVEFILTERS on the fact table clears its expanded table, which takes the part, plant and date filters with it;
VALUES ( dim_date[date] ), evaluated before that, puts the period's dates back. A part
with nothing on the shelf then reads blank, which is the truth.
Averages fail the same way. AVERAGEX ignores blanks, so for a part that was stocked six months of twelve it averages six
balances, not twelve. Divide by the number of snapshot dates instead.
The DAX
Inventory Value =
SUM ( fact_inventory_snapshot[ext_cost] )
-- The last snapshot date in the period, for the whole company. Clearing every
-- filter but the date (part, plant, family, anything) means a part not stocked
-- on that date reads blank, instead of falling back to an older balance.
Closing Inventory =
VAR LastSnapshot =
CALCULATE (
MAX ( fact_inventory_snapshot[snapshot_date] ),
REMOVEFILTERS ( fact_inventory_snapshot ),
VALUES ( dim_date[date] )
)
RETURN
CALCULATE ( [Inventory Value], dim_date[date] = LastSnapshot )
-- Every month end in the period counts, including the ones where
-- a part had no stock, so part rows add up to the total.
Average Month-End Inventory =
VAR Snapshots =
CALCULATETABLE (
VALUES ( fact_inventory_snapshot[snapshot_date] ),
REMOVEFILTERS ( fact_inventory_snapshot ),
VALUES ( dim_date[date] )
)
RETURN
DIVIDE ( SUMX ( Snapshots, [Inventory Value] ), COUNTROWS ( Snapshots ) ) For contrast, the two measures that produced the wrong rows above:
-- Looks right on a card, wrong in a table by part
Closing (LASTNONBLANK) =
CALCULATE (
[Inventory Value],
LASTNONBLANK ( dim_date[date], [Inventory Value] )
)
-- AVERAGEX skips blanks, so months with no stock drop out of the average
Average (skips empty months) =
AVERAGEX ( VALUES ( fact_inventory_snapshot[snapshot_date] ), [Inventory Value] )