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 ...
  • 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))
    )
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