Cumulative total by month of type of data

[Anonymous]

Hello To all

I'm struggling with a cumulative total by Month that I need.

I need to show by month the total number of rows of a given state but adding the ones of the previous month.

I have the following table:

id status month 

1   x           1

2   x           1

3   x           2

4   x           3

5   x           3

6   x           3

7   x           4

So in this case in month 1 I want to show 2, on 2 show 3 (2+1), on 3 show 6 (2+1+3) and so on.

I'm using the following formula:

Cumulative_actual = CALCULATE(COUNT(ChangeStatus[id]);
FILTER(ChangeStatus;ChangeStatus[Month] <= MAX(ChangeStatus[Month])))

But it is not adding. I also tried using SUM, instead of count and the result was the same.

Regards

[Vvelarde]

@Anonymous

Hi, try adding ALL Function

Cumulative_actual = CALCULATE(COUNT(ChangeStatus[id]);
FILTER(ALL(ChangeStatus);ChangeStatus[Month] <= MAX(ChangeStatus[Month])))

[Anonymous]

@Vvelarde

Thanks, It seems ALL helped in the rigth direction but...

If I use a simple graphic of state by month I get these values:

4    10

5     5267

6     6

But the cumulative:

4       6155

5       18693

6        20067

It seems it is not filtering... due to the ALL I presume since it "cleans" the filters.

Should I use ALLExcept?

[v-caliao-msft]

@Anonymous,

Could you please share more information about your data and expected result? So that we can make further analysis.

Regards,

Charlie Liao

[Anonymous · Accepted solution]

Hello

@v-caliao-msft, I tried with allexcept and it worked.

This was the formula:

Cumulative_actual = CALCULATE(DISTINCTCOUNT(ChangeStatus[id]);
FILTER(ALLEXCEPT(ChangeStatus;ChangeStatus[MyFilterColumn]);
ChangeStatus[Month] <= MAX(ChangeStatus[Month])))

This way the result was correctly calculated.

Thanks.