Cumulative sum with condition

[mhendel]

Hello

This is my situation : 

Date	Amount	Condition	Cumulative if Condition = Y
01-01-18	0		0
01-02-18	0		0
01-03-18	0		0
01-04-18	0		0
01-05-18	0		0
01-06-18	0		0
01-07-18	0		0
01-08-18	0		0
01-09-18	0		0
01-10-18	57896	Y	57896
01-11-18	0		57896
01-12-18	0		57896
01-01-19	0		57896
01-02-19	0		57896
01-03-19	0		57896
01-04-19	0		57896
01-05-19	0		57896
01-06-19	115000	Y	172896
01-07-19	0		172896
01-08-19	25000	N	172896
01-09-19	0		172896
01-10-19	0		172896
01-11-19	38125	Y	211021
01-12-19	56000	N	211021
01-01-20			211021

The 3 first columns are the data in.  The last column is the result i'd like to have : a cumulative sum based on date, with condition set to Y.  I tried to build a column with a CALCULATE function but i missed something...

Could you help me?

Thanks in advance and sorry for my poor English.

Michael Hendeles

[v-gizhi-msft · Accepted solution]

Hi,

Please try to create a calculated column first:

New Amount = IF('Table'[Condition]="N",0,'Table'[Amount])

Then try this measure:

Measure = CALCULATE(SUM('Table'[New Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])))

The result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[mhendel]

@v-gizhi-msft  : thanks! You make my day!

[camargos88]

Hi @mhendel ,

Try creating this measure:

Running Total = CALCULATE(SUM('Table'[Amount]); FILTER(ALL('Table'[Date]); 'Table'[Date] <= MAX('Table'[Date])); 'Table'[Condition] = "Y")

Ricardo

[mhendel]

@camargos88  : i forgot the @...

[mhendel]

Thanks for your help

Here is the result with your measure : 

Date	Running Total
01-10-18 00:00	57896
01-11-18 00:00	57896
01-12-18 00:00	57896
01-01-19 00:00	57896
01-02-19 00:00	57896
01-03-19 00:00	57896
01-04-19 00:00	57896
01-05-19 00:00	57896
01-06-19 00:00	172896
01-07-19 00:00	172896
01-08-19 00:00	172896
01-09-19 00:00	172896
01-10-19 00:00	172896
01-11-19 00:00	211021
01-12-19 00:00	211021
01-01-20 00:00	211021

Unfortunately, the rows with an amount of 0 are missing...

In fact i had the same result...

[amitchandak]

@mhendel ,

Try like

Cumm Sales = CALCULATE(SUMX(Table,if(Table[Condition]="Y",Table[Amount],0)),filter(date,date[date] <=max(Table[ Date])))

Or

Cumm Sales = CALCULATE(SUMX(Table,if(Table[Condition]="Y",Table[Amount],0)),filter(All(Table),Table[date] <=max(Table[ Date])))
//old
Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(all(Table),Table[date] <=max(Table[ Date])),Table[Condition]="Y")

Also, try the option with old Formula

What error you are getting

[mhendel]

@amitchandak 

[amitchandak]

after table[Amount] it will be  ; not ,

Cumm Sales = CALCULATE(SUMX(Table;if(Table[Condition]="Y";Table[Amount];0));filter(All(Table);Table[date] <=max(Table[ Date])))

[amitchandak]

@mhendel ,

Try like

Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(date,date[date] <=max(Table[ Date])),Table[Condition]="Y")

[mhendel]

@amitchandak 

Thanks for your help,

When i try your measure, i receive an error... Are you sure with your syntax?