Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
moia79
Helper I
Helper I

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  



5 REPLIES 5
parry2k
Super User
Super User

@moia79 here is the output:

 

parry2k_0-1693953091497.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@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

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.