Complex conditional summing

Hi ClaudioF,

Thank you for reaching out to the Microsoft fabric community forum. Thank you techies, for your inputs on this issue.

You're certainly not alone in finding sequential stock allocation logic challenging in DAX, as it is not inherently procedural.

The positive news is that what you're aiming to achieve is feasible in Power BI using a calculated column. Here's the approach that works:

1. Create a running total of the QtyRequested per order, sorted by OrderID.
2. Compare this cumulative quantity to your total available stock (e.g., 21 units).
3. If the cumulative total is less than or equal to the stock, mark the order as "Available"; otherwise, mark it as "Not Available".

Here’s a simplified version of the DAX formula you can use for a calculated column:

Status =

VAR Stock = 21

VAR CumQty =

    CALCULATE (

        SUM ( Orders[QtyRequested] ),

        FILTER (

            Orders,

            Orders[OrderID] <= EARLIER ( Orders[OrderID] )

        )

    )

RETURN

    IF ( CumQty <= Stock, "Available", "Not Available" )

Just make sure your table has a proper OrderID or sort column to maintain row order, and you can adjust the stock reference if it comes from another table.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.