Cumulative Sum By Month

[Stuznet]

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 easy but I'm not sure how hard to write the DAX function measure.

Measure = 
VAR A = CALCULATE([Total],FILTER(Data,Data[Column1] = "ABC" && Data[Month] = "January")) 
VAR B= CALCULATE([Total],FILTER(Data,Data[Column1]  = "ABC" && Data[Month] = "February"))
VAR C = CALCULATE([Total],FILTER(Data,Data[Column1] = "ABC" && Data[Month] = "March"))

RETURN 

CALCULATE(ABS(A),(B + A),(C + B)

I created this but I'm getting The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

[v-yulgu-msft]

Hi @Stuznet,

Please show us some sample data of 'Data' table. And what is the formula of [Total]? Besides, show us your desired output in Power BI. 

How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[Stuznet · Accepted solution]

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

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

[Stuznet]

@v-yulgu-msfthere is the dataset. The total is the Value. My desired output is create a Cumulative Sum field that adds up the "Value"

Month	Value	Cumulative
January	63	63
February	55	118
March	30	148
April	33	181
May	50	231
June	60	291
July	69	360
August	64	424
September	60	484
October	15	499
November	14	513
December	13	526
	526	3838