Hi @ClaudioF ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you @Anonymous for the prompt response.
It sounds like the core issue here isn't the data source, but how the DAX logic is handling stock depletion across filtered orders. You're right in identifying that even after stock is exhausted, the DAX measure continues to evaluate orders as if stock is still available which results in incorrect behavior.
What’s Going Wrong:
DAX measures don’t maintain row-by-row “state” like procedural code. So something like this:
CALCULATE (
SUM ( Orders[Quantity] ),
FILTER ( Orders, Orders[Quantity] <= [RemainingStock] )
)
doesn’t subtract quantities across rows, it just filters based on a static condition.
Here's some steps to fix it:
1. Rank the Orders
Assign a rank based on order priority (Order Number or Date).
OrderRank = RANKX(ALL(Orders), Orders[OrderDate], , ASC, DENSE)
2. Calculate Cumulative Demand
In a calculated column, add up the quantities of all prior orders.
CumulativeDemand =
CALCULATE (
SUM ( Orders[Quantity] ),
FILTER (
Orders,
Orders[OrderRank] <= EARLIER ( Orders[OrderRank] )
)
)
3. Determine If Stock Is Allocated
Only allocate if the cumulative demand is still within available stock.
IsAllocated =
IF ( Orders[CumulativeDemand] <= [TotalAvailableStock], 1, 0 )
You can then filter or sum orders with IsAllocated = 1 to get correct fulfillment values.
If the logic gets messy in DAX, this is actually easier to handle in Power Query with a grouped table and a running total column that lets you stop allocation once stock is exhausted.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you.
