Forum Discussion
Cumulative sum with condition
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 ...
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:
Hope this helps.
Best Regards,
Giotto
Hi Anonymous ,
Try creating this measure:
Running Total = CALCULATE(SUM('Table'[Amount]); FILTER(ALL('Table'[Date]); 'Table'[Date] <= MAX('Table'[Date])); 'Table'[Condition] = "Y")
Ricardo
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...
Anonymous ,
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
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])))
