Forum Discussion

Hossam_Khattab's avatar
Hossam_Khattab
Regular Visitor
3 months ago
Solved

Days Forward Coverage (DFC) Measure

The dashboard here shows a projected inventory ( a dynamic projected inventory which is calculated for every day based on the elements above it)

Dynamic Projected Inventory = Dynamic Selected Supply - Dynamic Selected Demand

Dynamic Selected Supply = Starting Inventory + Quality Inventory + Shipment Notification + PCH Order + Planned Order + Standard Production Fixed + Standard Production Planned

Dynamic Selected Demand = Customer Order + Forecast + Forecast Distribution Demand

What I want to see is the Days Forward Coverage (DFC), it is mainly to see how many days (starting from the next day) can I supply with my currently projected inventory.
"Starting from the next day" This is because the demand of today is aready taken into the calculation of the projected inventory I currently have.

This is an example to make it clear,

So for example, Let's analyse Day 1, we have 1000 projected inventory that can cover the demand of Day 2 and Day 3 but in Day 4 the remaining projected inventory of Day 1 will be (1000-500-400=100) so it will not cover Day 4 then we take the fraction (the remaining inverntory / the demand that can't be supplied) -> (100/200=0.5) 

So, the days that inventory of Day 1 can cover is 2.5 Days.


Note:

  1. I don't want to use the average logic by taking the demand average of a period and dividing the inventory over it because it is not a real acual output of the days can be supplied.
  2. I am working daily not weekly.

     

You will find the Power BI file on this google drive

https://drive.google.com/file/d/1r-cYz9bxQDo6i1_7MkhmGSmLaMg8souP/view?usp=drive_link 

 

I hope it is now clear enough and feel free to ask about any further clarification.

 

  • grazitti_sapna's avatar
    grazitti_sapna
    3 months ago

    Hi Hossam_Khattab,

     

    Try below DAX

     

    DFC (Days Supply) =
    VAR CurrentDate =
        MAX('Date'[Date])

    VAR InventoryAvailable =
        [Dynamic Projected Inventory]

    VAR FutureDays =
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] > CurrentDate
        )

    VAR DemandTable =
        ADDCOLUMNS(
            FutureDays,
            "DailyDemand", CALCULATE([Dynamic Selected Demand]),
            "CumDemand",
                VAR d = 'Date'[Date]
                RETURN
                CALCULATE(
                    [Dynamic Selected Demand],
                    FILTER(
                        ALL('Date'[Date]),
                        'Date'[Date] > CurrentDate &&
                        'Date'[Date] <= d
                    )
                )
        )

    VAR FirstExceededDay =
        MINX(
            FILTER(DemandTable, [CumDemand] > InventoryAvailable),
            'Date'[Date]
        )

    VAR FullDaysCovered =
        COUNTROWS(
            FILTER(
                DemandTable,
                [CumDemand] <= InventoryAvailable
            )
        )

    VAR PrevCumDemand =
        MAXX(
            FILTER(
                DemandTable,
                'Date'[Date] < FirstExceededDay
            ),
            [CumDemand]
        )

    VAR RemainingInventory =
        InventoryAvailable - COALESCE(PrevCumDemand,0)

    VAR ExceededDayDemand =
        MAXX(
            FILTER(
                DemandTable,
                'Date'[Date] = FirstExceededDay
            ),
            [DailyDemand]
        )

    VAR FractionalDay =
        DIVIDE(RemainingInventory, ExceededDayDemand, 0)

    RETURN
    IF(
        ISBLANK(FirstExceededDay),
        FullDaysCovered,
        FullDaysCovered + FractionalDay
    )
     
    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!
  • Hossam_Khattab's avatar
    Hossam_Khattab
    3 months ago

    Find the Correct logic working code below,

    DFC =
    VAR CurrentDate = MAX('Date'[Date])
    VAR CurrentInventory = [Dynamic Projected Inventory]

    RETURN
    IF(
        ISBLANK(CurrentInventory),
        BLANK(),  -- If Inventory is blank, return BLANK and stop here
       
        -- Otherwise, proceed with the full calculation
        VAR FutureDemandTable =
            FILTER(
                ALLSELECTED('Date'),
                'Date'[Date] > CurrentDate
            )

        VAR CumulativeDemandTable =
            GENERATE(
                FutureDemandTable,
                VAR RowDate = 'Date'[Date]
                RETURN
                    ROW(
                        "RunningTotalDemand",
                        SUMX(
                            FILTER(FutureDemandTable, 'Date'[Date] <= RowDate),
                            [Dynamic Selected Demand]
                        ),
                        "DailyDemand", [Dynamic Selected Demand]
                    )
            )

        VAR FullDays = COUNTROWS(FILTER(CumulativeDemandTable, [RunningTotalDemand] <= CurrentInventory))

        VAR MaxFullDemand = MAXX(FILTER(CumulativeDemandTable, [RunningTotalDemand] <= CurrentInventory), [RunningTotalDemand])
        VAR RemainingInventory = CurrentInventory - IF(ISBLANK(MaxFullDemand), 0, MaxFullDemand)

        VAR NextDayDemand =
            MINX(
                FILTER(CumulativeDemandTable, [RunningTotalDemand] > CurrentInventory),
                [DailyDemand]
            )

        VAR FractionalDay = DIVIDE(RemainingInventory, NextDayDemand, 0)

        RETURN
        IF(CurrentInventory <= 0, 0, (IF(ISBLANK(FullDays), 0, FullDays) + FractionalDay))
    )

5 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    3 months ago

    Hi Hossam_Khattab,

     

    Try below DAX

     

    DFC (Days Supply) =
    VAR CurrentDate =
        MAX('Date'[Date])

    VAR InventoryAvailable =
        [Dynamic Projected Inventory]

    VAR FutureDays =
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] > CurrentDate
        )

    VAR DemandTable =
        ADDCOLUMNS(
            FutureDays,
            "DailyDemand", CALCULATE([Dynamic Selected Demand]),
            "CumDemand",
                VAR d = 'Date'[Date]
                RETURN
                CALCULATE(
                    [Dynamic Selected Demand],
                    FILTER(
                        ALL('Date'[Date]),
                        'Date'[Date] > CurrentDate &&
                        'Date'[Date] <= d
                    )
                )
        )

    VAR FirstExceededDay =
        MINX(
            FILTER(DemandTable, [CumDemand] > InventoryAvailable),
            'Date'[Date]
        )

    VAR FullDaysCovered =
        COUNTROWS(
            FILTER(
                DemandTable,
                [CumDemand] <= InventoryAvailable
            )
        )

    VAR PrevCumDemand =
        MAXX(
            FILTER(
                DemandTable,
                'Date'[Date] < FirstExceededDay
            ),
            [CumDemand]
        )

    VAR RemainingInventory =
        InventoryAvailable - COALESCE(PrevCumDemand,0)

    VAR ExceededDayDemand =
        MAXX(
            FILTER(
                DemandTable,
                'Date'[Date] = FirstExceededDay
            ),
            [DailyDemand]
        )

    VAR FractionalDay =
        DIVIDE(RemainingInventory, ExceededDayDemand, 0)

    RETURN
    IF(
        ISBLANK(FirstExceededDay),
        FullDaysCovered,
        FullDaysCovered + FractionalDay
    )
     
    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!
  • v-menakakota's avatar
    v-menakakota
    Community Support
    3 months ago

    Hi Hossam_Khattab ,
    Thanks for reaching out to the Microsoft fabric community forum. 


    I would also take a moment to thank  grazitti_sapna  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 
    I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

    Best Regards, 
    Community Support Team

    • v-menakakota's avatar
      v-menakakota
      Community Support
      3 months ago

      Hi Hossam_Khattab ,

      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

      Best Regards, 
      Community Support Team



  • Hossam_Khattab's avatar
    Hossam_Khattab
    Regular Visitor
    3 months ago

    Find the Correct logic working code below,

    DFC =
    VAR CurrentDate = MAX('Date'[Date])
    VAR CurrentInventory = [Dynamic Projected Inventory]

    RETURN
    IF(
        ISBLANK(CurrentInventory),
        BLANK(),  -- If Inventory is blank, return BLANK and stop here
       
        -- Otherwise, proceed with the full calculation
        VAR FutureDemandTable =
            FILTER(
                ALLSELECTED('Date'),
                'Date'[Date] > CurrentDate
            )

        VAR CumulativeDemandTable =
            GENERATE(
                FutureDemandTable,
                VAR RowDate = 'Date'[Date]
                RETURN
                    ROW(
                        "RunningTotalDemand",
                        SUMX(
                            FILTER(FutureDemandTable, 'Date'[Date] <= RowDate),
                            [Dynamic Selected Demand]
                        ),
                        "DailyDemand", [Dynamic Selected Demand]
                    )
            )

        VAR FullDays = COUNTROWS(FILTER(CumulativeDemandTable, [RunningTotalDemand] <= CurrentInventory))

        VAR MaxFullDemand = MAXX(FILTER(CumulativeDemandTable, [RunningTotalDemand] <= CurrentInventory), [RunningTotalDemand])
        VAR RemainingInventory = CurrentInventory - IF(ISBLANK(MaxFullDemand), 0, MaxFullDemand)

        VAR NextDayDemand =
            MINX(
                FILTER(CumulativeDemandTable, [RunningTotalDemand] > CurrentInventory),
                [DailyDemand]
            )

        VAR FractionalDay = DIVIDE(RemainingInventory, NextDayDemand, 0)

        RETURN
        IF(CurrentInventory <= 0, 0, (IF(ISBLANK(FullDays), 0, FullDays) + FractionalDay))
    )