Running total by ignoring negative values

[abssenthil_pbi]

Hi All,

I am looking for a help where i need to calculate the running total by ignoring the negative values

below is the sample where i need to calculate C by adding A and only positive numbers of B 

C = SUM(Table[A])+IF(SUM(Table[B)>0,SUM(Table[B]),0)  works fine

and then calculate running total of C but its not working as expected for ex on Jul-18 i should get 600664 instead 600000

C Cumulative = CALCULATE(SUM(Table[A])+IF(SUM(Table[B)>0,SUM(Table[B]),0),filter(ALL(Table),Table[Month]<=MAX(Table[Month])))

tried C Cumulative = CALCULATE([C],filter(ALL(Table),Table[Month]<=MAX(Table[Month])))

or 

C Cumulative = CALCULATE([C],filter(ALL(Dates),Dates[Date]<=MAX(Dates[Date])))

can someone help me with this?

Month	A	B	C	C Cumulative
Jan-17			0	0
Feb-17		0	0	0
Mar-17		0	0	0
Apr-17		0	0	0
May-17		-4637	0	0
Jun-17	300000	0	300000	300000
Jul-17		0	0	300000
Aug-17		0	0	300000
Sep-17		0	0	300000
Oct-17		0	0	300000
Nov-17		-253	0	300000
Dec-17		0	0	300000
Jan-18		-37	0	300000
Feb-18		0	0	300000
Mar-18		0	0	300000
Apr-18		0	0	300000
May-18		-1	0	300000
Jun-18		-2	0	300000
Jul-18	300000	664	300664	600000
Aug-18		530	530	600000
Sep-18		27842	27842	624107
Oct-18	30000	424	30424	654531
Nov-18		-5892	0	648639
Dec-18	12000	0	12000	660639

[abssenthil_pbi]

Thanks again. Hope the below information is clear

My direct table looks like below

Month	Type	A	B
Jan-17	Org
Feb-17	Org		0
Mar-17	Org		0
Apr-17	Org		0
May-17	Org		-4637
Jun-17	Org	300000	0
Jul-17	Org		0
Aug-17	Org		0
Sep-17	Org		0
Oct-17	Org		0
Nov-17	Org		-253
Dec-17	Org		0
Jan-18	Org		-37
Feb-18	Org		0
Mar-18	Org		0
Apr-18	Org		0
May-18	Org		-1
Jun-18	Org		-2
Jul-18	Org	300000	664
Aug-18	Org		530
Sep-18	Org		27842
Oct-18	Org	30000	424
Nov-18	Org		-6400
Nov-18	Adj		507
Dec-18	Org	12000	0
Dec-18	Adj		9
Dec-18	Adj1		-5

My calculated column C & Cumulative should be like this

C=(A+ only +ve values of B)

Month	A	B	C=(A+only +ve of B)	C Cumulative
Jan-17			0
Feb-17		0	0
Mar-17		0	0
Apr-17		0	0
May-17		-4637	0
Jun-17	300000	0	300000	300000
Jul-17		0	0	300000
Aug-17		0	0	300000
Sep-17		0	0	300000
Oct-17		0	0	300000
Nov-17		-253	0	300000
Dec-17		0	0	300000
Jan-18		-37	0	300000
Feb-18		0	0	300000
Mar-18		0	0	300000
Apr-18		0	0	300000
May-18		-1	0	300000
Jun-18		-2	0	300000
Jul-18	300000	664	300664	600664
Aug-18		530	530	601194
Sep-18		27842	27842	629036
Oct-18	30000	424	30424	659460
Nov-18		-5893	0	659460
Dec-18	12000	4	12004	671464

My final output should be

[lbendlin · Accepted solution]

cumul =
VAR d =    MAX ( 'Table'[Month] )
VAR t =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( 'Table' ), [Month] <= d ),
        'Table'[Month],
        "a", SUM ( 'Table'[A] ),
        "b", SUM ( 'Table'[B] )
    )
RETURN    0  + SUMX ( t, [a] + MAX ( 0, [b] ) )

[lbendlin]

Here is one version using CALCULATE

cumul = 
var d = max('Table'[Month])
return calculate(sum('Table'[A]),ALLSELECTED('Table'),'Table'[Month]<=d)+calculate(sum('Table'[B]),ALLSELECTED('Table'),'Table'[Month]<=d,'Table'[B]>0)

Here is another one using SUMX

cumul2 = 
var d = max('Table'[Month])
return SUMX(FILTER(ALLSELECTED('Table'),'Table'[Month]<=d),[A]+max([B],0))

[abssenthil_pbi]

Hi @lbendlin Thanks for the response. Its better than the old calc but not working as expected since we have more details at the table. Just to simplify i had given the summarized values at month level but the actual table with direct query has few other dimensions which breaks one row in to 1000's of rows. 

The filter 'Table'[B]>0 applies at row level and it considers all positive adjusted rows in that month but the expectation is to summarize the adjusted value of that month and if that is>0 then consider.

In the below example Nov-18 has one +ve row with 507 and the overall adjusted for that month is -5892. The provided formula just adds 507 to the prev month value in the running total.

Month	A	B	C	C Cumulative
Jan-17			0
Feb-17		0	0	0
Mar-17		0	0	0
Apr-17		0	0	0
May-17		-4637	0	0
Jun-17	300000	0	300000	300000
Jul-17		0	0	300000
Aug-17		0	0	300000
Sep-17		0	0	300000
Oct-17		0	0	300000
Nov-17		-253	0	300000
Dec-17		0	0	300000
Jan-18		-37	0	300000
Feb-18		0	0	300000
Mar-18	24299	0	24299	324299
Apr-18		0	0	324299
May-18		-1	0	324299
Jun-18		-2	0	324299
Jul-18	300000	664	300664	624963
Aug-18		530	530	625494
Sep-18	81600	27842	109442	734936
Oct-18	30000	424	30424	765360
Nov-18		-5892	0	765867	507
Dec-18	12000	-68	12000	777867
Jan-19		1	1	777871

[lbendlin]

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.