Cumulative sum by month does not produce the right result

[Dunner2020]

Hi there,

I have a data which is as shown in the figure:

I have a measure which calculates a value let say TotalCustomerDuration which can be calculated using the following measure:

TotalCustomerDuration = Sumx(Table, [NumberofConsumers]*[Duration])/91577

I want to calculate the Cumulative Sum of the TotalCustomerDuration by month.  I wrote the cumulative sum as follow:

Cumulative Sum = 

var CurrentDate = MAX('Table'[Date])

Return

sumx( filter(ALL('Table'),'Table'[Date]<=CurrentDate ), [TotalCustomerDuration ])

This measure produces the following output:

As you can see the cumulative sum is not producing the right result. I am not sure where am I making the mistake. Any help would be appreciated. Data can be download from here https://1drv.ms/u/s!AucycxZHFe9TjVP95dR9t35xwz0k?e=ALfxOk 

[v-rzhou-msft · Accepted solution]

Hi @Dunner2020 

Please check the type of your date column, you can build a rank column to have a test.

Rank column:

Rank = RANKX('Table','Table'[Date],,ASC,Dense)

I use your measure to have a test, and it works well.

Result is as below.

You can download the pbix file from this link: Cumulative sum by month does not produce the right result

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Dunner2020]

@amitchandak , I tried the 

Cumulative Sum =
calculate( [TotalCustomerDuration],filter(ALL('Table'),'Table'[Date]<=MAX('Table'[Date]) ))

But still, it gave the same result.

[amitchandak]

@Dunner2020 , Formula seems fine at first look.

Try like

Cumulative Sum =
calculate( [TotalCustomerDuration],filter(ALL('Table'),'Table'[Date]<=MAX('Table'[Date]) ))

Cumulative Sum =
calculate( sum(Table[TotalCustomerDuration]),filter(ALL('Table'),'Table'[Date]<=MAX('Table'[Date]) ))