Help with DAX for Running Total Subtractions

[bibro5]

I'm working with two tables, **Inventory** and **Transactions**, and I'm trying to create a DAX measure that provides a running total of subtractions. Here's the scenario:

Inventory
ID	Type	Quantity
1	apples	5
2	bananas	3
3	oranges	13
4	grapes	7
transactions
DATE	ID	Quantity sold
1/1/2025	2	2
1/3/2025	2	3
1/4/2025	1	1
1/5/2025	3	4

In the example, there are 5 bananas sold over the first two days. Since the inventory quantity is 3, I need to show 2 bananas sold on 1/1/2025 and 1 banana sold on 1/3/2025. The remaining transaction on 1/3/2025 should not be counted since there would have only been 3 bananas in inventory.

My goal is to stop counting item transactions once the inventory runs out. How can I write the DAX measures to achieve this?

Thanks in advance for your help!

[bibro5]

@quantumudit thank you with your reply and you are correct. I could have been more descriptive on what I wanted the outcome to be.

In my example, I don't want to use a slicer for each individual item. I want it to look like my screenshot. The only issue is the total for running quantity sold is greater than available quantity. can you help me fix this?

[quantumudit · Accepted solution]

Hello @bibro5 

To obtain the correct row totals, we modify the formulas as follows:

Modify the "Available Quantity" formula as follows:

Available Quantity =
IF ( [Total Quantity Sold] <> BLANK (), SUM ( Inventory[Quantity] ) )

Modify the "Running Quantity Sold" formula as follows:

Running Quantity Sold = 
VAR _remainingQty =
    CALCULATE (
        [Available Quantity] - [Total Quantity Sold],
        FILTER (
            ALL ( Transactions ),
            Transactions[ID] = MAX ( Transactions[ID] )
                && Transactions[DATE] <= MAX ( Transactions[DATE] )
        )
    )
VAR _runningQuntity =
    IF (
        _remainingQty >= 0,
        [Total Quantity Sold],
        [Total Quantity Sold] + _remainingQty
    )
VAR _handlingOversale =
    IF (
        _runningQuntity <= 0
            && [Total Quantity Sold] <> BLANK (),
        0,
        _runningQuntity
    )

RETURN
    IF(ISINSCOPE(Transactions[DATE]), _handlingOversale, [Available Quantity])

In the following animation, you can observe that we are achieving the desired results at both the total level and the individual ID/Type leve. While I understand you do not wish any slicer, please note that the formula is robust to handle it should you decide to utilize one in the future.

 

I am also attaching the Power BI file for your reference.

I hope have answered your question correctly. However, if there is still any doubt, please do not hesitate to let me know.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a Super User

[quantumudit]

Hello @bibro5 

Thank you for providing the sample data. It would have been beneficial to increase the number of rows in the "Transactions" table and include an expected outcome based that data. Nevertheless, I added additional data to the "Transactions" table to validate edge cases.

The "Transaction" table I created is as follows:

DATE	ID	Quantity sold
2025-01-01	2	2
2025-01-03	2	3
2025-01-04	1	1
2025-01-05	3	4
2025-01-06	3	5
2025-01-07	3	3
2025-01-08	4	7
2025-01-09	4	3
2025-01-10	3	7
2025-01-11	1	2
2025-01-12	1	3
2025-01-13	1	4
2025-01-14	3	8
2025-01-15	3	3

The relationship between the 2 tables are as follows:

Here are the two initial simple measures I have developed:

To calculate the total quantity sold from the "Transactions" table:

Total Quantity Sold =
SUM ( Transactions[Quantity sold] )

To calculate/reference the available quantity from the "Inventory" table: 

Available Quantity =
IF ( [Total Quantity Sold] <> BLANK (), MAX ( Inventory[Quantity] ) )

Here is the measure I have created that calculated the "Running Subtraction" of quantity based on their availability in the inventory:

Running Quantity Sold = 
VAR _remainingQty =
    CALCULATE (
        [Available Quantity] - [Total Quantity Sold],
        FILTER (
            ALL ( Transactions ),
            Transactions[ID] = MAX ( Transactions[ID] )
                && Transactions[DATE] <= MAX ( Transactions[DATE] )
        )
    )
VAR _runningQuntity =
    IF (
        _remainingQty >= 0,
        [Total Quantity Sold],
        [Total Quantity Sold] + _remainingQty
    )
VAR _handlingOversale =
    IF (
        _runningQuntity <= 0
            && [Total Quantity Sold] <> BLANK (),
        0,
        _runningQuntity
    )
RETURN
    _handlingOversale

Here is the screenshot of the measure along with the result:

As you see, for "oranges," on January 10th, the quantity sold is 7, but in Inventory, only 1 item remains. Therefore, it shows 1 instead of 7 (ignores the remaining 6). Additionally, if any sales for "oranges" are found afterward, since we have none in inventory it shows 0.

I am attaching the Power BI file for your reference. Please review the results and, and inform me if you have any further queries.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a Super User