Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
@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.
@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.:
Hi,
Show the exact result that you are expecting with an explanation.
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
105 | |
98 | |
38 | |
30 |