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

DateInventoryDemand 1Demand 2Demand 3InboundCoverage
9/5/2023100050 10            220
9/12/202394050 5            213
11/11/2023885505007            153
11/18/202332850 10300           153
12/2/202356875 5            139
1/6/202448850 7            104
1/13/202443175 10300              97
4/12/2024646502505               89
4/19/2024341150 7               82
5/4/202418450 10               67
5/11/202412475 5300              60
7/10/202434450 7  



Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.

       

  • 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