Model daily stock positions in BI without adding balance snapshots, and define how missing captures and incomplete reporting periods are handled.
Index
End-of-day stock is a position rather than a movement. Adding daily stock snapshots creates a total with no meaning for the quantity actually available at closing.
The design to commission
Separate receipt and dispatch facts from position snapshots. Balance measures need the appropriate snapshot for the selected period, while turnover may depend on an average agreed with operations. Document behavior for days without capture: carrying the last known balance can be useful but must appear as carried information rather than a measurement that day. Product-master changes must not change the identity of older positions. Compare proposal calculations for monthly closing, categories and sites, including incomplete periods. Do not assess only a daily screen where all records are present. Existing models may be usable; remodeling becomes justified when positions and movements are mixed and no measure can explain its result. Specify how users inspect the actual snapshot date before treating a displayed quantity as current availability.
Supplier criteria
- Distinguish positions from period receipts and dispatches.
- Make actual snapshot dates inspectable with the indicator.
- Handle missing captures through explicitly approved rules.
Acceptance with an exception
For hypothetical acceptance, filter a week containing a day with no snapshot. The total must represent the agreed position and show the gap without adding other daily balances to fill it.
Reference for assessing scope
Prepare your project with Quantum9
Bring Quantum9 daily positions and movements for the same product. Comparing them reveals whether the issue lies in capture, the data model or the measure used in reporting.