I am trying to create a running total of On Hand inventory by doing some Math in PowerBI. I’d like to take the existing quantity on hand and include quantity ordered for the orders for each inventory item. As the running total is calculated I’d like to use the most recently calculated value, Something like in this forum (How to create a running total in PowerBI for real-time inventory levels).
But my data model is crazily different from this.
The running total is calculated as ‘Inventory’[Sum of Inventory Value]+ Detailed Messages[Sum of Quantity Available] + ‘Open Orders’[Sum of Units - Order/Transaction Quantity] - Demand Pegging [Sum of Quantity Ordered].
A caveat is that if the previous month sum is a negative, the value carried forward should be a Zero.
Below is my data model, am not sure what I am doing wrong but my running total does not seem to work.
Find below the calculations in excel
calculations.xlsx (12.4 KB)
find the pbix file and sample dataset
data.zip (635.2 KB)