Forum Discussion
vipett
Helper III
Helper IIICalculate projected stock
I've tried a lot of examples from previous posts about this topic, both from this site and other sites but still cant get it to work,
See below for a non-working solution and how my data is structured:
This is my table "To output"
Where the inventory balance is the current forecast, Fct is the forecasted consumption, Demand date is the first date of each month Rolling 12, Stock use just puts the inventory balance to the first demand date and 0 on the other dates, OpenPO is incoming purchase orders. Qty is just the sum of Stock Use-Fct+OpenPO.
This is the current output:
As you can see, it is not cumulating the figures as it should.
Stock projection in November should be 241, December 239,etc
Expected stock change = (SUM( ToOutput[Fct] )*-1)+SUM(ToOutput[OpenPO])
Stock Level = Max(ToOutput[Stock Use])
Stock projection =
VAR ExpectedChange =
CALCULATE(
[Expected stock change],
FILTER(
ALL( 'Calendar Demand' ) ,
'Calendar Demand'[Date] <= MAX( 'Calendar Demand'[Date] )
)
)
VAR StockLevel =
CALCULATE(
[Stock level] ,
FILTER(
ALL( 'Calendar Demand' ) ,
'Calendar Demand'[Date] <= MAX( 'Calendar Demand'[Date] )
)
)
RETURN
ExpectedChange + StockLevel
What am I doing wrong here?
- Was apparently as easy as using a quick measure..Qty running total in Demand date =CALCULATE(SUM('ToOutput'[Qty]),FILTER(ALLSELECTED('ToOutput'[Demand date]),ISONORAFTER('ToOutput'[Demand date], MAX('ToOutput'[Demand date]), DESC)))
1 Reply
vipettWas apparently as easy as using a quick measure..Qty running total in Demand date =CALCULATE(SUM('ToOutput'[Qty]),FILTER(ALLSELECTED('ToOutput'[Demand date]),ISONORAFTER('ToOutput'[Demand date], MAX('ToOutput'[Demand date]), DESC)))