Days Forward Coverage (DFC) Measure

[Hossam_Khattab]

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 · Accepted solution]

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 · Accepted solution]

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]

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]

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

[FBergamaschi]

Hi @Hossam_Khattab 

I downloaded the file but I am not sure I get what you are asking.

You already have a measure, DFC, is that not working?

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[grazitti_sapna · Accepted solution]

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!