Dax for Cumulative sum of a measure on line chart

[Anonymous]

Hi there,

I have following measure that counts distinct clients for a condition:

M1 = CALCULATE(DISTINCTCOUNT(table(client_id), filter(table,table[Served?] ="Yes" )) 

I want to show cumulative sum values over months(I have a datedim table) of this measure on a line chart.

Please suggest how can I do it?

My sample pbi file: https://1drv.ms/u/s!Ag919_pO_UKrgQbUO5qfsTPxwfY8?e=gmwvsD

Thanks in advance.

[Anonymous]

I have following measure that counts distinct clients for a condition:

M1 = CALCULATE(DISTINCTCOUNT(table(client_id), filter(table,table[Served?] ="Yes" )) 

I want to show cumulative sum values over months(I have a datedim table) of this measure on a line chart.

Please suggest how can I do it?

My sample pbi file: https://1drv.ms/u/s!Ag919_pO_UKrgQbUO5qfsTPxwfY8?e=gmwvsD

Thanks in advance.

[sevenhills · Accepted solution]

Change MAX(Datedim[Date]) to MAX(Test_table[date]))

[camargos88]

Hi @Anonymous ,

Try this measure:

M2 = CALCULATE(DISTINCTCOUNT(Test_table[Client id]), FILTER(ALL(Datedim[Date]), Datedim[Date] <= MAX(Datedim[Date])), Test_table[Served?] = "Y")

 

[Anonymous]

@camargos88 thanks it works! If you can answer this for me I'll be very thankful- How can I limit this cumulative sum to the current month on the graph, so that it shows the cumulative sum only till October 2020 not for the rest of the months in the year-nov, dec.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[camargos88]

@Anonymous ,

You can change the measure for it:

M2 = CALCULATE(DISTINCTCOUNT(Test_table[Client id]), FILTER(ALL(Datedim[Date]), Datedim[Date] <= MAX(Test_table[date])), Test_table[Served?] = "Y")

or change you datedim to the max date of test_table. 

Datedim = CALENDAR(DATE(2020,1,1), MAX(Test_table[date]))

[sevenhills · Accepted solution]

Change MAX(Datedim[Date]) to MAX(Test_table[date]))