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