Forum Discussion

caleboud's avatar
caleboud
Frequent Visitor
5 years ago

Running Total for inventory planning negative values.

I'm calculating planned inventory per month, using 2 running Totals, Demand and Underway stock Starting from Onhand stock the calculation is simple : Planned Inventory = Onhand - Demand + Underway ...
Need Help
v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi , caleboud ;

According to your description, I create a simple example and create a measure as followws:

plann inventory = 
var _plan=CALCULATE(SUM([Onhand])+SUM([Underway])-SUM([Demand]),FILTER(ALL('Table'),[Date]<=MAX([Date])))
return IF(_plan<0,0,_plan)

The final output is shown below:

if it's incorrect, can you share me more scene with screenshot about you question?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

caleboud's avatar
caleboud
Frequent Visitor
5 years ago

Thank you soo much for your fast reply.

But I think i didn't explain my problem well.

Starting from my onhand i use running totals to calculatel my future inventory and that runs perfect.

The issue starts when my demand > stock + income

And as you suggest, you can put a blank or 0 where the calculation is <0 see as below

I added test Calc inv to show where i have the negative value (-324)

And as you suggested in Inv QTY I added the condition if < 0 then 0.

The problem with my running total is that the next month the addition includes again - 324.

What i want is that it continues from 0 not -324

In the example the Inv QTY of the next month is 848 while it should be 1172 (thats + 324)

so it's a bit more comlicated.
Any solution to calculate my future inv is welcome, maybe there are other ways than the running total way...