Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
Hi v-zhengdxu-msft.
It works perfectly, thank you very much for your help, it is much appreciated!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |