Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Use cycle For / While for calculating inventory coverage

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 cov...
Need Help
parry2k's avatar
parry2k
Super User
2 years ago

Anonymous 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

 

 

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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