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
camargos88 : i forgot the @...
