topic Re: Inventory development rolling monthly values in DAX Commands and Tips

Inventory development rolling monthly values

Anonymous — Wed, 25 May 2022 12:51:02 GMT

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

Re: Inventory development rolling monthly values

tamerj1 — Wed, 25 May 2022 14:08:15 GMT

Hi Anonymous
You have to have a date table

Re: Inventory development rolling monthly values

Anonymous — Sun, 29 May 2022 09:33:39 GMT

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 🤔

Re: Inventory development rolling monthly values

Anonymous — Wed, 22 Jun 2022 07:13:48 GMT

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

Thank you.

Re: Inventory development rolling monthly values

tamerj1 — Wed, 22 Jun 2022 07:21:13 GMT

Anonymous

Can you share a sample file?