Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Inventory development rolling monthly values

Hello PBI Community,

 

I have a actual Inventory value changed on a daily base and ingoing (+ Pur) and outgoing (-Sales) movements (values) on stock show in sum in the below screenshot as Sum stock move.

This is my table in the PBI Desktop

 Table call "test"

 

Now I created 2 Measures to calculate the monthly rolling Forecast of the stock value:

 

Measure = If(MAX('test'[Index])=0,0,CALCULATE(SUM('test'[Stockmov]),FILTER('test','test'[TRANSDATE].[Date])))
 
Measure 2 = sum(Inv[PostedValue]) + CALCULATE(SUMX('test',[Measure]),'test'[TRANSDATE].[Monat] <= MAX('test'[TRANSDATE].[Monat]))
 
The result in the table look for the step from 2022 May to 2022 June correct, but than the calculation run wrong 
 In July (Juli 2022) I need the value of 19.773.180 -361.126,04 - 436.210,20

 

Can anyone help me with my problem. I can't get any further and can't find a solution.
Thank you in advance.
Kind regards
Mary

 

 

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    You have to have a date table

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello tamerj1, can you please help me to find a solution. I can´t find any help and solution.

      Thank you.

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Anonymous 

        Can you share a sample file?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello tamerj1.

      I filled in now a calendartable and linked it with the test table. But on the end I still get not the right result.

       

      Measure3 = IF(MAX('test'[Index])=0,0,CALCULATE(MAX(test[Stockmov]),FILTER(ALL(test),[Index]=MAX('test'[Index])-1)))
       
      Measure 4 = sum(Inv[PostedValue])-CALCULATE(SUMX(test,[Measure3]),FILTER(ALL('tbm VDIM_Calendars'),[MONTH]<=MAX('tbm VDIM_Calendars'[MONTH])))
       

      I think my Measure 4 is not correct. Because from the 2nd day it always takes the “Act Stock value” as the base value and not Act Stock value minus 1st move minus 2nd move minus…..

      And I have another problem 🙄

      I have different countries in the test table. If I use the INDEX "0" in the Measure, it does not work when filtering the countries for evaluation. Maybe Measure3 would have to take the smallest index and the oldest date per Country filter available in the test table for the calculation. Or if no INDEX is used - one or more conditions in Measure3 have to be added 🤔