cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Dynamically subtracting values in rows

I am not sure the best way to articulate my need, but I have a data set that looks similar to the below:

Key         Item ID   Expiration     Qty On Hand
19384    123456    7/24/2024    100
17405    123456    7/24/2024    50
13858    123456    7/24/2024    10
10958    356789    6/1/2024      20
19485    356789    1/12/2024    60
19385    987654    8/20/2024    30

I need to add a custom column that calculates the qty at risk based on the date and other criteria. The calculation is no problem, but the problem is the products can have different date, and the total qty at risk must be subtracted by lines. This is not a set column I am subtracting and the quantities being subtracted on each line will change daily. For example, the total qty at risk for item 356789 is 30. So 30 should be subtracted from 60, and 0 should be subtracted from 20. For item 123456, 120 are at risk but all dates are the same so 100 should be subtracted from 100, then 20 from 50, then 0 from 20. An example of the desired output is below.

Key         Item ID    Expiration   Qty On Hand Qty At Risk
19384    123456    7/24/2024    100                100
17405    123456    7/24/2024    50                  20
13858    123456    7/24/2024    10                  0
10958    356789    6/1/2024      20                  0
19485    356789    1/12/2024    60                  30
19385    987654    8/20/2024    30                  0

Essentially I need to group by item, by key, and by date, subtract, and then subtract quantites from each date until the total quantity on hand is zero or we have a remainder. How can I calculate this type of running total without removing the keys or dates? I have tried some techniques of grouping by count all rows and trying to convert to lists or add temporary tables but I am getting stuck. Any input appreciated.

2 REPLIES 2
Super User

what's the expected output?

have you tried to use SUMMARIZE function?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

The expected output is listed above in the initial post. I would not be able to use SUMMARIZE or any DAX expressions as this does need to be done in PowerQuery, so that it can live in the Dataflow. If done at the report level it would have to be done hundreds of times.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors