Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Inventory Forecast Recursive Calculation

Hi all, I'm trying to create a DAX measure that populates "forecasted remanining inventory".   I have gone through Gerhard's blog on recursive calculation, but it talks about compound interest and ...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi Anonymous,

 

Is this your expected result?  You may download the file from here.

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Ashish, thank you so much. 


What if I have a new inventory value in February, and I'm supposed to take that value for the DATESYTD calculation? Do I use the LASTNONBLANK function?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    I think my solution should still work.  Please try it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Ashish, no it doesn't, if I have another value for SKU A in February 2018 (say 400), your measure totals up the inventory values for the past two months. which means the remaining inventory for February will become 900 - (28 + 22) = 850.

       

      The february inventory data should override the previous inventory data and the calculation should be carried forward. I.e. the february forecast value should be 400-22 = 378, and march forecast should be 378 - 16 = 362 etc etc.

       

      (I'm trying to use :

      CALCULATE ( SUM ( Inventory[Qty] ) , LASTNONBLANK(...))

       but I am still stumbling (I'm still quite new at DAX)).

       

      Thank you!

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        I cannot quite appreciate your example.  First, there should also be Purchases data. so that opening stock + Purchases - Sales = Clsoing stock.  In your example, the inv qty for Feb should be 472-22=450.

         

        Am i incorrect?