Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
JessicaRohrer
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
ryan_mayu
Super User
Super User

@JessicaRohrer 

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!




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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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