Forum Discussion
Solved
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 a...
Worked it out using existing response:
https://community.fabric.microsoft.com/t5/Desktop/closing-stock-calculation/m-p/2607672
marcosvin
Resolver II
Resolver IIHello!
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)