Forum Discussion

naoyixue1's avatar
naoyixue1
Icon for Post Patron rankPost Patron
3 years ago

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

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!

Need Help

6 Replies

  • naoyixue1's avatar
    naoyixue1
    Icon for Post Patron rankPost Patron
    3 years ago

    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's avatar
    naoyixue1
    Icon for Post Patron rankPost Patron
    3 years ago

    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's avatar
    naoyixue1
    Icon for Post Patron rankPost Patron
    3 years ago

    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. 

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        naoyixue1
        Icon for Post Patron rankPost Patron
        3 years ago

        Anonymous Thanks Xiaoxin. Let me check that. Really appreicate that. Will let you know after test.