Cumulative Column But only adding if previous row was negative

[BC123]

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

[BC123]

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?

[BC123]

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.

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[FreemanZ]

hi @BC123 ,

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]+_NegAcc

it worked like: