Forum Discussion

Stuznet's avatar
Stuznet
Helper V
7 years ago
Solved

Cumulative Sum By Month

I'm pulling all my hairs out for this..   I have a static column value and I need to create a new measure to sum it.  Sample. A2 is (=A) B2 is (=B1 + A2) and so on   I know this looks ea...
Need Help
Show and Tell
Tutorial Requests
  • Stuznet's avatar
    Stuznet
    7 years ago

    v-yulgu-msftI figured it out. Thank you for looking into this.

     

    I turned the Month Name into Month number and changed the new calculated type to decimal/number.

    Month Num = 
            IF(Table1[Month] = "January","1",
            IF(Table1[Month] = "February","2",
            IF(Table1[Month] = "March","3",
            IF(Table1[Month] = "April","4",
            IF(Table1[Month] = "May","5",
            IF(Table1[Month] = "June","6",
            IF(Table1[Month] = "July","7",
            IF(Table1[Month] = "August","8",
            IF(Table1[Month] = "September","9",
            IF(Table1[Month] = "October","10",
            IF(Table1[Month] = "November","11",
            IF(Table1[Month] = "December","12"))))))))))))

    Then, I created a measure 

    Cumulative = CALCULATE([Total],FILTER(ALLSELECTED(Table1),Table1[Month Num] <= MAX(Table1[Month Num])))

    I changed ALL to ALLSELECTED otherwise the value will be static.

Stuznet's avatar
Stuznet
Helper V
7 years ago

v-yulgu-msftI figured it out. Thank you for looking into this.

 

I turned the Month Name into Month number and changed the new calculated type to decimal/number.

Month Num = 
            IF(Table1[Month] = "January","1",
            IF(Table1[Month] = "February","2",
            IF(Table1[Month] = "March","3",
            IF(Table1[Month] = "April","4",
            IF(Table1[Month] = "May","5",
            IF(Table1[Month] = "June","6",
            IF(Table1[Month] = "July","7",
            IF(Table1[Month] = "August","8",
            IF(Table1[Month] = "September","9",
            IF(Table1[Month] = "October","10",
            IF(Table1[Month] = "November","11",
            IF(Table1[Month] = "December","12"))))))))))))

Then, I created a measure 

Cumulative = CALCULATE([Total],FILTER(ALLSELECTED(Table1),Table1[Month Num] <= MAX(Table1[Month Num])))

I changed ALL to ALLSELECTED otherwise the value will be static.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks, it is almost can't get the results.  AllSelected (Table) Not AllSelected (Table.Column)