Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

amitchandak's avatar
amitchandak
Super User
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak 

 

 

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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])))