Use cycle For / While for calculating inventory coverage

[moia79]

dear DAX gurus,

I have a dataset that shows the inventory projection over the next months, given different demands and the supply planned. I would need to calculate, in each row, which is the coverage of the inventory, meaning how much of future demand can be fulfilled with the inventory.

I thought of using a cicle for/while, calculating the sum of the demand between now and a day in the future that continue to move forward till the sum of demand is greater than the inventory

FOR (x = 0, x = 999, x++

       if ( inventory - sum of ( Demand 1, Demand 2, Demand 3 ) calculated for the next "x" days > 0,

            x++,
           inventory coverage = x )

could you please suggest me if that FOR / WHILE is possible and how you would code it?

my database:

Date	Inventory	Demand 1	Demand 2	Demand 3	Inbound	Coverage
9/5/2023	1000	50		10		220
9/12/2023	940	50		5		213
11/11/2023	885	50	500	7		153
11/18/2023	328	50		10	300	153
12/2/2023	568	75		5		139
1/6/2024	488	50		7		104
1/13/2024	431	75		10	300	97
4/12/2024	646	50	250	5		89
4/19/2024	341	150		7		82
5/4/2024	184	50		10		67
5/11/2024	124	75		5	300	60
7/10/2024	344	50		7

[parry2k]

@moia79 here is the output:

[parry2k]

@moia79 try this and let me know how it goes, add following measures:

Sum Inventory = SUM ( Inventory[Inventory] )
Sum Inbound = SUM ( Inventory[Inbound] )
Sum Demand = SUM ( Inventory[Demand 1] ) + SUM ( Inventory[Demand 2] ) + SUM ( Inventory[Demand 3] )

Inbound Coverage Days Inventory = 
VAR __DateInContext = MAX ( 'Inventory'[Date] )
VAR __DateTable =
ADDCOLUMNS ( 
    FILTER ( 
        ALL ( 'Inventory'[Date] ), 
        'Inventory'[Date] >= __DateInContext 
    ) , 
    "@Demand", [Sum Demand],  
    "@Inventory", CALCULATE ( [Sum Inventory], 'Inventory'[Date] = __DateInContext ),  
    "@InBound", CALCULATE ( [Sum Inbound], 'Inventory'[Date] = __DateInContext )
)
VAR __DemandRunningTotal = 
ADDCOLUMNS ( 
    __DateTable, 
    "@RTDemand", 
        VAR __Date = [Date] 
        RETURN 
        SUMX ( FILTER ( __DateTable, [Date] <= __Date ), [@Demand] ) 
)
VAR __InventoryOutDate = MINX ( FILTER( __DemandRunningTotal, ( [@Inventory] + [@InBound] ) <= [@RTDemand] ), [Date] )
VAR __InventoryOutDateLast = MAXX ( __DemandRunningTotal, [Date] )
VAR __ActualInventoryOutDate = COALESCE ( __InventoryOutDate, __InventoryOutDateLast )
VAR __CoverageDays = DATEDIFF ( __DateInContext, __ActualInventoryOutDate, DAY )
RETURN 
__CoverageDays

[moia79]

hello,

unfortunately it does not work - I think the dataset is more complex than my example.

I however solved using a sort of "brute force attack", i.e.:

Coverage (days) =

IF (ShortageReport[Inventory projection EoM] < calculate(sum(ShortageReport[Ordered, Quantity]),filter(ShortageReport,ShortageReport[RM / WIP Item Number]=earlier(ShortageReport[RM / WIP Item Number]) && ShortageReport[Start Date]>=earlier(ShortageReport[Start Date]) && ShortageReport[Start Date] <= earlier(ShortageReport[Start Date])+0)),0,

IF (ShortageReport[Inventory projection EoM] < calculate(sum(ShortageReport[Ordered, Quantity]),filter(ShortageReport,ShortageReport[RM / WIP Item Number]=earlier(ShortageReport[RM / WIP Item Number]) && ShortageReport[Start Date]>=earlier(ShortageReport[Start Date]) && ShortageReport[Start Date] <= earlier(ShortageReport[Start Date])+7)),7,

if (ShortageReport[Inventory projection EoM] < calculate(sum(ShortageReport[Ordered, Quantity]),filter(ShortageReport,ShortageReport[RM / WIP Item Number]=earlier(ShortageReport[RM / WIP Item Number]) && ShortageReport[Start Date]>=earlier(ShortageReport[Start Date]) && ShortageReport[Start Date] <= earlier(ShortageReport[Start Date])+14)),14,

.... 

I kept increasing the amount of days by 7 till covering 2 years. It's a long formula but it works.

If you have any idea how to transform into a FOR WHILE loop, I'll be happy to test

thanks

[Ashish_Mathur]

Hi,

Show the exact result that you are expecting with an explanation.

[moia79]

Hello @Ashish_Mathur 

the result is calculated manually in the last column "Coverage", which is the difference between the date of the inventory and the date when the stock will be depleted considering the future demand

e.g.: on 9/5/2023, the inventory is 1.000 units. If you sum the demand, without considering the inbound, it will be depleted with the demands on 4/12/2024 -> coverage is 4/12/2024 - 9/5/2023 = 220 days.