Cumulative total +ve and -ve in separate column

[CAZaidRazvi]

Hi

I needed help with a problem that I'm facing, 

I have more than 100 schedules that are uniquely identifiable, Now each schedule has one column which has a first row that is negative  say USD (-10,000) and subsequet 60 rows that as positive say  USD 200,

Now if i cumulatively total this column it will become 0 on row 50 and they after become positive.

Now i Need to showcase each schedule separately to show which is +ve and which is still -ve at any given point of time. I need the total all Positive and Negative columns separately at any given date.

[v-xinruzhu-msft · Accepted solution]

Hi @CAZaidRazvi 

You can refer to the following solution.

1.Add an index column in power query

2.Create the following calculated column.

Cumm =
VAR a =
    MIN ( 'Table'[Index] )
VAR b =
    LOOKUPVALUE ( 'Table'[Collection], 'Table'[Index], a )
RETURN
    b
        - SUMX (
            FILTER ( 'Table', [Index] <= EARLIER ( 'Table'[Index] ) && [Index] <> a ),
            [Collection]
        )

+VE = if([Cumm]>0,[Cumm])

-VE = IF([Cumm]<0,ABS([Cumm]))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CAZaidRazvi]

Thanks a lot, Much Appretiated

I had been picking my brains out for quite some time on solving this.