Inventory development rolling monthly values

[Anonymous]

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

[tamerj1]

Hi @Anonymous 
You have to have a date table

[Anonymous]

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

Thank you.

[tamerj1]

@Anonymous 

Can you share a sample file?

[Anonymous]

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 🤔