The situation I’m looking to solve is forecasting when inventory will run out. I want to calculate this by taking Inventory and subtracting the sales order quantity up to the last sales orders date. After there are no more sales orders to subtract from, start subtracting by the daily average sales to determine when that inventory will hit 0.
There are a few measures that i created that I believe help set this up.
InventorybyGroup = CALCULATE(SUM('Sample Inv Data'[Inventory]), ALLEXCEPT('Sample Inv Data', 'Sample Inv Data'[Cust Item Loc]))
Runtot = CALCULATE(SUM('Sample SO Data'[Quantity]), FILTER(ALLSELECTED('Sample SO Data'), 'Sample SO Data'[Shipment Date]<=MAX('Sample SO Data'[Shipment Date]) && YEAR('Sample SO Data'[Shipment Date]) = YEAR(MAX('Sample SO Data'[Shipment Date]))))
Forecasted Inv = [InventorybyGroup] - [Runtot]
As you can see I can get the [Forecasted Inv] measure to subtract up to the last Shipment Date.
Would it be possible to continue the dates listed after the last shipment date and then keep on subtracting by the daily average?
I have attached a PBIX file with all of the corresponding calculations except for the subtractions of the daily average sales. How would I be able to approach this?
Thanks for any help! Let me know if I need to provide more context for this situation.
Sample Inventory Forecasting.pbix (842.3 KB)
Desired results :