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

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 🤔