Days of Stock - dax calculation

[tzanouman]

Hello team, 

I want your support please regarding the Days of Stock calculation. The calculation should be: 

Ending Stock / sum(of next 4 weeks Total Stock)*30. 

Any idea what the formula can be for each cell to get dynamically the sum of the next 4 weeks of Total stock?

CodeID	Week	TOTAL STOCK	ENDING STOCK	Days of Stock
802946	202401	4585	2400
802946	202402	3500	2400	?
802946	202403	3000	2400	?
802946	202404	2500	2400	?
802946	202405	1500	2400	?
802946	202406	1000	2500	?
802946	202407	5000	4000	?
802946	202408	4585	4000	?
802946	202409	3500	4000	?
802946	202410	3000	2400	?
802947	202401	4585	2400
802947	202402	3500	2400	?
802947	202403	3000	2400	?
802947	202404	2500	2400	?
802947	202405	1500	2400	?
802947	202406	1000	2500	?
802947	202407	5000	4000	?
802947	202408	4585	4000	?
802947	202409	3500	4000	?
802947	202410	3000	2400	?

Thanks in advance,

Manolis

[v-zhengdxu-msft · Accepted solution]

Hi @tzanouman 

please try this:

First of all, I create a index column in Power Query:

Then I create a calculate column:

Days of Stock = 
VAR _4Days = FILTER(
                'Table',
                'Table'[Index]<EARLIER('Table'[Index])+5
                &&
                'Table'[CodeID]=EARLIER('Table'[CodeID]
                )
            )
VAR _1Days = FILTER(
                'Table',
                'Table'[Index]<EARLIER('Table'[Index])+1
                &&
                'Table'[CodeID]=EARLIER('Table'[CodeID]
                )
            )
RETURN IF(
        CALCULATE(SUM('Table'[TOTAL STOCK]),
        FILTER(
            'Table',
            'Table'[Index]=EARLIER('Table'[Index])+4
            &&
            'Table'[CodeID]=EARLIER('Table'[CodeID])))<>BLANK(),
            SUMX(
                _4Days,
                'Table'[TOTAL STOCK]
            )
            -
            SUMX(
                _1Days,
                'Table'[TOTAL STOCK]
            )
        )

The result is as follow:

Best Regards,

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tzanouman]

Hi v-zhengdxu-msft.

It works perfectly, thank you very much for your help, it is much appreciated!