Forum Discussion
Cumulative Column But only adding if previous row was negative
Hi All,
I'm trying to create a calc column that is cummulative but only with negative values. Ive tried a bunch of solutions but havent had much luck so any help is appreciated!
This is my code(Note: It does just a regular cummulative atm):
VAR Expiry = CALCULATE(SUM('Table'[Qty]), FILTER('Table', EARLIER('Table)'[Date])>='Table'[Date]))
Example Table:
| Date | Qty | Expired |
| 1/1/2023 | 30 | 30 |
| 1/4/2023 | 20 | 20 |
| 1/5/2023 | -10 | -10 |
| 1/10/2023 | -60 | -70 |
| 1/11/2023 | 100 | 30 |
5 Replies
FreemanZSuper User
hi Anonymous ,
try to add a calculated column like:
Column = VAR _NegAcc = SUMX( FILTER( data, data[Qty]<0 &&data[date]<EARLIER(data[date]) ), data[Qty] ) RETURN [Qty]+_NegAccit worked like:
- Ahmedx
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
This is pretty close, the issue is that the negative values should decrease(if postitive) or increase(if negative) depending on the Qty and not just be a sum of all negative vaules. I've include a example that showcases that a bit better. This ones got me stumped. Thank you for you time!
Date Qty Expired 1/1/2023 30 30 1/4/2023 -20 -20 1/5/2023 10 -10 1/10/2023 40 30 1/11/2023 -50 -50 1/12/2023 10 -40 1/1/2024 -5 -45 1/2/2024 30 -15 1/3/2024 20 5 - Ahmedx
what result do you expect?
Effectivly what I showed in the table, im creating this to find expected expiry stock. so for each date period any positive values(expired stock) would not be added to the next date but any negative values(remaining consumption) would be added to the calculation of next dates expired stock. The issue is it should really only be factoring whatever the previous date expired stock would be in calculating the current dates expired stock.
