Forum Discussion

prices35's avatar
prices35
New Member
9 years ago
Solved

Cumulative sum without date field

I am trying to create a Cumulative Sum field that adds up the "Every Chargeback" column.  I am looking at all data from 2011 though today and breaking that up by the "CHGBACK DAY OF MONTH".  The prob...
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi prices35,

     

    To calculate the cumulative total for the "Every Chargeback" column, you can create a measure like below:

     

    Cumulative Every Chargeback = CALCULATE(SUM('Date Chrgback'[Every Chargeback]),FILTER(ALL('Date Chrgback'),'Date Chrgback'[CHGBACK DAY OF MONTH]<=MAX('Date Chrgback'[CHGBACK DAY OF MONTH])))

     

     

    Best Regards,
    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Hi prices35,

 

To calculate the cumulative total for the "Every Chargeback" column, you can create a measure like below:

 

Cumulative Every Chargeback = CALCULATE(SUM('Date Chrgback'[Every Chargeback]),FILTER(ALL('Date Chrgback'),'Date Chrgback'[CHGBACK DAY OF MONTH]<=MAX('Date Chrgback'[CHGBACK DAY OF MONTH])))

 

 

Best Regards,
Qiuyun Yu

Anonymous's avatar
Anonymous
Not applicable
9 years ago

It works but if you want filter by antother field doesn't work, for example if I add a month name column =

switch ([CHGBACK DAY OF MONTH]

, 1

,"January"

,2

,"February"... etc )

 

 

How can I do that ?

 

Regards

  • ewuchatka's avatar
    ewuchatka
    Helper II
    9 years ago

    Thanks for this very helpful post. Did you manage to figure this out? I'm looking for exactly the same solution e.g. managed to do the cumulative sum for all data but if I filter on the data (e.g. on the cost centre), it doesn't calculate the cumulative appropriately.

    • ewuchatka's avatar
      ewuchatka
      Helper II
      9 years ago

      I've figured it out :)!!!

       

      Add VALUES at the end...

       

      Cumulative = CALCULATE(
      SUM('Sheet2 (2)'[Actual]),
      FILTER(ALL('Sheet2 (2)'),'Sheet2 (2)'[Period]<=MAX('Sheet2 (2)'[Period])), VALUES('Sheet2 (2)'[Cost Centre]))