Forum Discussion
Help with DAX for Running Total Subtractions
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
Super UserHello 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