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 | |
| 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
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
10 Replies
Anonymous ,
Try like
Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(date,date[date] <=max(Table[ Date])),Table[Condition]="Y")
Thanks for your help,
When i try your measure, i receive an error... Are you sure with your syntax?
Hi Anonymous ,
Try creating this measure:
Running Total = CALCULATE(SUM('Table'[Amount]); FILTER(ALL('Table'[Date]); 'Table'[Date] <= MAX('Table'[Date])); 'Table'[Condition] = "Y")RicardoThanks 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
camargos88 : i forgot the @...
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
v-gizhi-msft : thanks! You make my day!
