cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

 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

5 REPLIES 5
Super User

@moia79 here is the output:

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.

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 =
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 =
__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.

Helper I

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

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors