Forum Discussion
Forecasted Closing Stock Position
Hi,
Im struggling to create DAX measure for a forecasted closing stock position using the start point of the latest stock position, deducting forecasted sales, adding existing open purchase orders and adding forecasted purchase orders to create a forward stock position for each week going forward.
The below Excel example should help to explain:
Worked it out using existing response:
https://community.fabric.microsoft.com/t5/Desktop/closing-stock-calculation/m-p/2607672
2 Replies
Worked it out using existing response:
https://community.fabric.microsoft.com/t5/Desktop/closing-stock-calculation/m-p/2607672
Hello!
Adding here some metrics to calculate last stock position value that might be helpful. The [Last Constant Stock] metric can be used in the metric that will do the additions and subtractions with the other forecast metrics.
Sum of Stock Qty = SUM(Table[Stock Qty])
Last Stock = CALCULATE(SUM(Table[Stock Qty]),LASTNONBLANK(Table[Date],[Sum of Stock Qty]))Last Constant Stock = CALCULATE([Last Stock],ALL(Table[Date]),MAX(Table[Date])>=Table[Date])This blog explains the use of the DAX LASTNONBLANK command very well:
DAX LASTDATE vs. LASTNONBLANK | Mitchellsql (mitchellpearson.com)
