Weeks of coverage calculation based on the forward looing demands in Power BI

[naoyixue1]

Hey all,

I got a problem to calculate the weeks of coveage based on the forward looking demands (see below example) 

Item A: Weeks of coverage = 8/11 = 0.72

Item A: Because the first 5 weeks demands is 33. By the end of the 5th weeks, the leftover inventory is about 18 = 51-1-1-5-11-15. Given that, we only can fulfill 90% of the 6th week demands, which is 18 / 20 = 0.9. SO Item B 's weeks of coverage is about 5.9. 

Do you know what logic or how we can apply in BI to calculate that? Thanks!

[naoyixue1]

Hey all,

That's my thoughts.  (see below example)  I'm now stuck to transfer that way of calculation into dax. Do you have any ideas how in dax? Thanks!

Steps:

1: Add the running total column to highlight the cumulative demands

2: mark "1" if we can hit that day's running total demands (day 1 thru day 6)

3: for the day which we cannot cover the running total (day 7). I will use the leftover which i have at the end of day 6 ( 9 = 100 - 91). And then use 9 units divided by that day (day 7) demands.  9 / 110 = 0.08. In other words, we only can cover 0.08 days of day 7 demands. 

4: add those days of coverage together. So total will be 6.08 days coverage. 

[v-shex-msft]

HI @naoyixue1,

I enter to the query editor to use the 'unpivot column' and 'replace value' function on your table to change its structure for following calculations.

Measure formula:

formula = 
VAR currItem =
    SELECTEDVALUE ( 'Table'[Item] )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [Item],
        [Inventory],
        [Days],
        "Rolling",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( ALLSELECTED ( 'Table' ), [Days] <= MAX ( 'Table'[Days] ) ),
                VALUES ( 'Table'[Item] )
            )
    )
VAR days =
    MAX (
        MAXX (
            FILTER ( summary, [Item] = currItem && [Rolling] <= [Inventory] ),
            [Days]
        ),
        1
    )
RETURN
    IF (
        days = 1,
        CALCULATE (
            DIVIDE ( MAX ( 'Table'[Inventory] ), MAX ( 'Table'[Value] ) ),
            FILTER ( ALLSELECTED ( 'Table' ), [Days] = 1 ),
            VALUES ( 'Table'[Item] )
        ),
        VAR rolling =
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( ALLSELECTED ( 'Table' ), [Days] <= days ),
                VALUES ( 'Table'[Item] )
            )
        RETURN
            days
                + CALCULATE (
                    DIVIDE ( MAX ( 'Table'[Inventory] ) - rolling, MAX ( 'Table'[Value] ) ),
                    FILTER ( ALLSELECTED ( 'Table' ), [Days] = days + 1 ),
                    VALUES ( 'Table'[Item] )
                )
    )

Regards,

Xiaoxin Sheng

[naoyixue1]

@v-shex-msft Thanks Xiaoxin. Let me check that. Really appreicate that. Will let you know after test. 

[naoyixue1]

Hey all, sorry i just check to see whehter you all have some thoughts? I really don't find any similar problems here. Thanks a lot!

[naoyixue1]

Hey all,

I know that might be tough without the sample BI table, so I just attach that BI table. Would you take a look about that? That will be helpful even you just share your thoughts. Thanks!

[naoyixue1]

Does anyone have any thoughts about how? Thanks!