Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have gone through the below Link.
https://community.powerbi.com/t5/Desktop/Days-of-supply/td-p/635223
I am not able to apply it to the below data.
See below Table. (Data is coming from Matrix Visual in Power BI)
Hope the above is clear.Showing Finally below the table is the Matrix visual on how i need to present It.
Been struggling for a long time.kindly help
any additional inputs requied let me know.
Regards
Joshi_M
Material | Location | Material Group | Total Opening Stock | Total Demand | Cumulative Openig Demand | Total Receipts | Cumulative Opening Receipts | Total Closing Stock | Week/Year | Index weeks |
R5 | A | B | 10 | 0 | 0 | 239 | 2022.18 | 0 | ||
R5 | A | B | 239 | 60 | 60 | 40 | 40 | 219 | 2022.19 | 1 |
R5 | A | B | 219 | 60 | 120 | 39 | 79 | 198 | 2022.2 | 2 |
R5 | A | B | 198 | 60 | 180 | 0 | 79 | 138 | 2022.21 | 3 |
R5 | A | B | 138 | 27 | 207 | 39 | 118 | 150 | 2022.22 | 4 |
R5 | A | B | 150 | 14 | 221 | 39 | 157 | 175 | 2022.23 | 5 |
R5 | A | B | 175 | 14 | 235 | 0 | 157 | 161 | 2022.24 | 6 |
R5 | A | B | 161 | 14 | 249 | 0 | 157 | 147 | 2022.25 | 7 |
R5 | A | B | 147 | 15 | 264 | 0 | 157 | 132 | 2022.26 | 8 |
R5 | A | B | 132 | 16 | 280 | 0 | 157 | 116 | 2022.27 | 9 |
R5 | A | B | 116 | 16 | 296 | 0 | 157 | 101 | 2022.28 | 10 |
R5 | A | B | 101 | 16 | 312 | 0 | 157 | 85 | 2022.29 | 11 |
R5 | A | B | 85 | 16 | 328 | 0 | 157 | 70 | 2022.3 | 12 |
R5 | A | B | 70 | 5 | 333 | 19 | 176 | 84 | 2022.31 | 13 |
R5 | A | B | 84 | 5 | 338 | 0 | 176 | 80 | 2022.32 | 14 |
Index weeks | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 |
Row Labels | 2022.18 | 2022.19 | 2022.2 | 2022.21 | 2022.22 | 2022.23 | 2022.24 | 2022.25 | 2022.26 | 2022.27 | 2022.28 | 2022.29 | 2022.3 | 2022.31 | 2022.32 |
Sum of Total Opening Stock | 239 | 219 | 198 | 138 | 150 | 175 | 161 | 147 | 132 | 116 | 101 | 85 | 70 | 84 | |
Sum of Total Demand | 10 | 60 | 60 | 60 | 27 | 14 | 14 | 14 | 15 | 16 | 16 | 16 | 16 | 5 | 5 |
Sum of Cumulative Openig Demand | 60 | 120 | 180 | 207 | 221 | 235 | 249 | 264 | 280 | 296 | 312 | 328 | 333 | 338 | |
Sum of Total Receipts | 0 | 40 | 39 | 0 | 39 | 39 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 19 | 0 |
Sum of Total Closing Stock | 239 | 219 | 198 | 138 | 150 | 175 | 161 | 147 | 132 | 116 | 101 | 85 | 70 | 84 | 80 |
Stock Cover Days | 44 |
Hi @Joshi_M,
You can try to use the following measure formula if helps:
supply days =
VAR currWeek =
MAX ( Table1[Index weeks] )
VAR closeStock =
CALCULATE (
SUM ( Table1[Act Stock] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Index weeks] >= 0
&& Table1[Index weeks] <= currWeek
),
VALUES ( Table1[Plant Text] ),
VALUES ( Table1[Material Group Family] ),
VALUES ( Table1[Material] )
)
VAR summary =
SUMMARIZE (
ALLSELECTED ( Table1 ),
[Plant Text],
[Material Group Family],
[Material],
[Index weeks],
"Demand",
CALCULATE (
SUM ( Table1[Total Demand] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Index weeks] >= currWeek
&& Table1[Index weeks] <= EARLIER ( Table1[Index weeks] )
),
VALUES ( Table1[Plant Text] ),
VALUES ( Table1[Material Group Family] ),
VALUES ( Table1[Material] )
)
)
VAR _lastweek =
MAXX ( FILTER ( summary, closeStock - [Demand] >= 0 ), [Index Week] )
RETURN
(_lastweek - currWeek)*7
Regards
Xiaoxin Sheng
Hi Sheng ,
Many thanks for Your response.
Need a little more of your help with the below part.
The [Act Stock] for me is a measure which goes like below
VAR closeStock = CALCULATE ( SUM ( Table1[Act Stock] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Index weeks] >= 0 && Table1[Index weeks] <= currWeek ), VALUES ( Table1[Plant Text] ), VALUES ( Table1[Material Group Family] ), VALUES ( Table1[Material] ) )
Highly appreciate your help on this.Thanks again
Hoping to hear from you.
Regards
Joshi_M
Hi Sheng ,
I have some progress. I see some values for supply days.
I changed the DAX for closeStock as below.
Rest all I kept as is.
I got the output (values) for supply days but it is not correct.
Showing the same static values for all Material and all weeks.
Please help to identify what may have gone wrong ans what needs to be changed.
Apppreciate your help
Regareds
Joshi _M
HI @Joshi_M,
I think this should be related to allexcept function, it will ignore the filter effects that are not declared in the function:
VAR closeStock = SUMX (
FILTER (
ALLSELECTED ( Table1 ),
[Plant Text] = EARLIER ( Table1[Plant Text] )
&& [Material Group Family] = EARLIER ( Table1[Material Group Family] )
&& [Material] = EARLIER ( Table1[Material] )
&& Table1[Index weeks] >= 0
&& ZBE_12WEEK_DEMAND_AP[Index weeks] <= currWeek
),
[Act Stock]
)
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI
Regards,
Xiaoxin Sheng
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |