DAX to calculate Week Stock

[Anonymous]

Hi,

I need to pedict the future week stock for each material.

I am able to create the table for the qty receivable from suppliers in the corresponding week, but not able to find the solution to calculate the Week Stock.

I have two tables:

Table 1 showing records for Receivable quantities in the below format shown below:

Week Num  COO       Plant        Material   Suppliers                 PO Qty      Weeks

27	JP	101	11	India	197	0
27	JP	101	11	Japan	187	0
27	JP	101	11	Germany	102	0
27	JP	101	11	AFFL	192	0
27	JP	101	11	FRIng	174	0
27	JP	101	11	3rd Party	486	0
27	JP	101	12	India	0	0
27	JP	101	12	Japan	0	0
27	JP	101	12	Germany	0	0
27	JP	101	12	AFFL	0	0
27	JP	101	12	FRIng	0	0
27	JP	101	12	3rd Party	0	0
27	JP	101	13	India	0	0
27	JP	101	13	Japan	0	0
27	JP	101	13	Germany	0	0
27	JP	101	13	AFFL	0	0
27	JP	101	13	FRIng	0	0
27	JP	101	13	3rd Party	0	0
27	IN	101	11	India	186	0
27	IN	101	11	Japan	176	0
27	IN	101	11	Germany	132	0
27	IN	101	11	AFFL	124	0
27	IN	101	11	FRIng	135	0
27	IN	101	11	3rd Party	494	0
27	IN	101	12	India	104	0
27	IN	101	12	Japan	128	0
27	IN	101	12	Germany	184	0
27	IN	101	12	AFFL	188	0
27	IN	101	12	FRIng	157	0
27	IN	101	12	3rd Party	416	0
27	IN	101	13	India	0	0
27	IN	101	13	Japan	0	0
27	IN	101	13	Germany	0	0
27	IN	101	13	AFFL	0	0
27	IN	101	13	FRIng	0	0
27	IN	101	13	3rd Party	0	0
27	KR	101	11	India	146	0
27	KR	101	11	Japan	190	0
27	KR	101	11	Germany	182	0
27	KR	101	11	AFFL	179	0
27	KR	101	11	FRIng	101	0
27	KR	101	11	3rd Party	518	0
27	KR	101	12	India	147	0
27	KR	101	12	Japan	187	0
27	KR	101	12	Germany	194	0
27	KR	101	12	AFFL	104	0
27	KR	101	12	FRIng	107	0

- This list can be appended with different combination of Week Num, COO, Plant, Material & Suppliers.

- 3rd Party calculation is Sum(India+Japan+Germany)

Table 2 showing the inventory, weekly consumption and the delayed PO quantities for all the material across different plants and Country.

COO             Plant     Material  Curr Inv    WklyUsg  Late PO

JP	101	11	395	357	231
JP	101	12	0	0	0
JP	101	13	0	0	0
IN	101	11	116	327	147
IN	101	12	135	267	57
IN	101	13	0	0	0
KR	101	11	99	9	152
KR	101	12	180	181	134
KR	101	13	46	195	89

I want to calculate the weekly Stock after every week (27, 28, 29, 30...........)

Steps to calculate Weekly stock are:

- Step1: Calculate Total Receivables from Suppliers (Sum(AFFL, FRIng & 3rd Party), i.e. a selective sum of the PO Qty for the Suppliers.  This issue is resolved.

- Step2: Week 27 (current week) Stock Calculation is Sum(PO Qty receivable in Step 1 + Current Inv + Late PO) - Weekly Usage

- Step3: Week 28 and onwards (next week and onwards) - Sum(PO receivable qty from the supplier in the corresponding month + Late PO + Week Stock of previous month, eg. Week 27 stock in this case) - weekly usage.

Attached the expected Report layout.Expected Report Layout

[v-juanli-msft]

Hi @Anonymous 

This is my file.

Best Regards

Maggie

[Anonymous]

Hi @v-juanli-msft 

Thanks for your file. But this does not resolve my problem. As per my data table the Weekly Stock for Week 27 is correct, but the subsequent weeks are wrong as it is using the same Current Inventory for other weeks as well. In this case the week 28 and onwards it should use the week stock as the current inventory for the next week stock calculation.

[v-juanli-msft]

Hi @Anonymous 

Merge column in Power query to create a new column [id] in both tables.

Then create a relationship between two tables.

Create measures

current sum =
CALCULATE (
    SUM ( 'Table 1'[PO Qty] ),
    FILTER ( 'Table 1', 'Table 1'[Suppliers] IN { "AFFL", "FRIng", "3rd Party" } )
)


previous sum =
CALCULATE (
    SUM ( 'Table 1'[PO Qty] ),
    FILTER (
        ALLSELECTED ( 'Table 1' ),
        'Table 1'[Suppliers] IN { "AFFL", "FRIng", "3rd Party" }
            && 'Table 1'[Suppliers] = MAX ( 'Table 1'[Suppliers] )
            && 'Table 1'[id] = MAX ( 'Table 1'[id] )
            && 'Table 1'[Week Num]
                = MAX ( 'Table 1'[Week Num] ) - 1
    )
)

Measure 2 =
VAR c1 =
    LOOKUPVALUE ( 'Table 2'[Curr Inv], 'Table 2'[id], MAX ( 'Table 1'[id] ) )
VAR w1 =
    LOOKUPVALUE ( 'Table 2'[WklyUsg], 'Table 2'[id], MAX ( 'Table 1'[id] ) )
VAR l1 =
    LOOKUPVALUE ( 'Table 2'[Late PO], 'Table 2'[id], MAX ( 'Table 1'[id] ) )
RETURN
    SWITCH (
        MAX ( [Week Num] ),
        27, [current sum] + c1 + l1 - w1,
        [current sum] + [previous sum] + c1 + l1 - w1
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Please see in My blog on week can help you. The only thing you can not do in power BI, Calculate July and use July to build August. You have to build a cumulative measure.

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123