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

The problem occurs when demand > ( underway + Onhand ) then my planned inventory < 0

My inventory should not be < 0 .

I can put Planned inventory on 0 in my dax but the next month the running totals continue to use the negative value.

So the goal is to put the Planned inventory on 0 when its <0 but this should be taken into account the next month in my running total.

 

 

Need Help

3 Replies

  • 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...