PlantOps BI

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.

fact_inventory_snapshot, 12 month ends in 2025, 200 parts. A part with no row at a month end was not stocked there.

MethodValueVerdictWhy
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] )

If a snapshot can arrive late for one plant, the company-wide last date can land before that plant's data. Then compute the last date per plant, keep the part filter cleared, and sum the plants. The figures on this page come from the demo CSV, and the site's tests check that the closing rows add up and that the LASTNONBLANK gap equals the stale parts exactly.

Inventory turns · Days on hand · About the demo dataset