Build an inventory balance from movement transactions

Inventory balance is a stock measured at a point in time. Receipts, sales, returns and adjustments are flows that change that stock. Sum signed movements through the chosen endpoint, including the required opening balance, rather than summing daily closing balances.

Define the item, location and unit grain before calculating. Adding quantities across incompatible units or omitting transfers can produce a numerically tidy but unusable inventory report.

Inspect the movement fixture

FactInventory.csv in the extension lab contains one synthetic SKU and six movements:

Each MovementID is unique. The opening entry is included once at the start of the observed history. The fixture is complete through January 6 for this exercise; it does not establish later real-world stock.

Calculate the balance at the endpoint

Import the table and relate DimDate[Date] to FactInventory[Date]. Use the date dimension for the display axis and period selections.

Closing Quantity =
VAR EndDate = MAX(DimDate[Date])
RETURN
    CALCULATE(
        SUM(FactInventory[QuantityDelta]),
        FILTER(ALL(DimDate), DimDate[Date] <= EndDate)
    )

The measure removes the calendar restriction needed to recover earlier movements, then applies the endpoint. SKU filters remain relevant because the expression does not clear FactInventory or an item dimension.

Microsoft documents filter-context modification in CALCULATE and filter removal in ALL. This example relies on the specified date relationship and uses an explicit opening entry.

Filter the display without discarding the opening history

Select January 3–6 on the date dimension. January 3's closing quantity should still be 120, because the opening 100 and January 2 receipt 50 precede the visible range but remain necessary to calculate the balance.

Summing only movements inside the visible range would begin with −30 and produce a misleading negative value. That calculation is net movement during the selected period, not closing stock.

Keep both measures if the business needs them, with distinct labels: Period Quantity Change and Closing Quantity.

Do not add daily balances across time

The six displayed closing quantities sum to 580, but 580 is not the ending inventory. The ending balance is 40. A stock measure is generally nonadditive over time under this definition.

If a report requires average daily inventory, define the calendar, completeness and weighting, then average appropriate daily balances. That is another measure, not an alternative interpretation of the sum.

At a total row, the cumulative measure above uses the latest date in context, yielding the closing balance at that endpoint. Explain this behaviour rather than forcing the total to equal the sum of daily rows.

Handle transfers and corrections deliberately

With multiple locations, a transfer normally produces an outbound movement for one location and an inbound movement for another. Company-wide quantity may remain unchanged while location balances change. Validate both legs and their units.

A corrected historical movement can change every later balance. Reprocess affected history and preserve the correction evidence. An incremental pipeline that updates only today's partition may leave old cumulative results inconsistent.

Never clamp negative balances to zero just to improve presentation. A negative value may identify a timing issue, missing receipt, duplicate sale or a business process that permits temporary negative stock.

Verify movements and snapshots separately

The extension script independently checks closing quantities 100, 150, 120, 125, 45 and 40. Compare those with the actual Power BI output and verify exact movement IDs for a disputed endpoint.

DAX and relationship behaviour remain application-review steps. A current physical count or authoritative snapshot would provide an additional real-world reconciliation source, but none is claimed in this synthetic fixture.

Exercise: duplicate the −80 sale movement in a copy. The ending balance becomes −40, exposing a key-quality problem. Restore uniqueness, then filter the visible dates to January 5–6 and confirm the correct closing values still include earlier history.

NeuraPath's Data Analytics with Generative AI course connects DAX cumulative calculations with stock-and-flow reasoning. A reliable inventory measure preserves opening history and states which dimensions support valid aggregation.

Continue learning

This article is part of the DAX measures and analytical correctness sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in DAX cohort measures: preserve the acquisition group.
• Continue with DAX currency conversion at the correct transaction grain.