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.
