topic Re: Future -Days of Supply in DAX Commands and Tips

Future -Days of Supply

Joshi_M — Wed, 04 May 2022 13:37:45 GMT

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)

Main Data is coming from a direct query.
Slicers selected for Location and Material Group.
Fields available in direct query - Material ,Location, Material Group,Demand and Receipts and Closing stock,Index weeks and Week/Year which is in text format.
Index weeks vailable in the direct query. Current week has index 0. can go beyond 52 weeks
All calculation need to move considering Index 0 and ahead.
Created a Measure for Future closing stock since future closing stock not correct in direct query
Already created maesures Total Closing stock ,Total Opening Stock Cumulative Demand & Cumulative Receipts.
Example of Closing stock measure created,Total Closing Stock = SUMX(FILTER(ALLEXCEPT(Table1,Table1[Plant Text],Table1[Material Group Family],Table1[Material]),Table1[Index weeks]>=0 && Table1[Index weeks]<=MAX(Table1[Index weeks])),[Act Stock])
Need a measure to create a measure for calculating supply days based on the above conditions
Example Index week 0 - Closing stock -239 = Days of supply = 6*7 + (4/14 )*7 shown only 1st week.

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

Re: Future -Days of Supply

Anonymous — Mon, 09 May 2022 02:21:27 GMT

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

Re: Future -Days of Supply

Joshi_M — Mon, 09 May 2022 11:17:07 GMT

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

IF( Sum(Table1[Index weeks])=0,Sum(Table1[Stock on Hand ending in To]),( Sum(Table1[Purchase Order in To])+Sum(Table1[Goods in Transit in To])-Sum(Tabele1[Demand in To])))

How to use this in below since CALCULATE (Sum( Table1[Act Stock]) indicates an error.

Note : i already have a measure for closestock as earlier mentioned .SUMX(FILTER(ALLEXCEPT(Table1,Table1[Plant Text],Table1[Material Group Family],Table1[Material]),Table1[Index weeks]>=0 && Table1[Index weeks]<=MAX(Table1[Index weeks])),[Act Stock]). Can this be used somehow in your solution which you gave...

Note : it is dynamic table and keeps changing.

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

Re: Future -Days of Supply

Joshi_M — Wed, 11 May 2022 16:13:43 GMT

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.

VAR closeStock=SUMX(

FILTER(ALLEXCEPT(Table1,Table1[Plant Text],Table1[Material Group Family],Table1[Material]),Table1[Index weeks]>=0 && ZBE_12WEEK_DEMAND_AP[Index weeks]<=currWeek),

[Act Stock])

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

Re: Future -Days of Supply

Anonymous — Tue, 24 May 2022 07:42:21 GMT

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