Cumulative total measure works with count but doesn't work with distinct count

[Quiny_Harl]

Hello,

I have the following measure to calculate the cumulative total of user IDs for each date:

Cumulative Total ID =
VAR MaxDate = MAX('Date'[date])
RETURN
CALCULATE(COUNT(table2[user_id]),FILTER(ALLSELECTED('Date'),'Date'[date]<=MaxDate))

It works just as expected:

However, when I use exactly the same measure to calculate the cumulative total for distinct user IDs, only replacing the COUNT with DISTINCTCOUNT, the result is not correct:

Cumulative Total Distinct IDs =
VAR MaxDate = MAX('Date'[date])
RETURN
CALCULATE(DISTINCTCOUNT(table2[user_id]),FILTER(ALLSELECTED('Date'),'Date'[date]<=MaxDate))

What could be the reason for that and how can I fix it?

[Anonymous]

Hi  @Quiny_Harl ,

You can modify the formula like this:

VAR MaxDate = MAX('Date'[date])
RETURN
CALCULATE(DISTINCTCOUNT(table2[user_id]),FILTER(ALLSELECTED('Date'),'Date'[date]<=MaxDate && 'table2[user_id]=max('table2[user_id])))

Best Regards,

Liu Yang

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

[Quiny_Harl]

Thank you for the suggestion!
The formula cannot work because the last argument of the filter statement, which is 'table2[user_id]=max('table2[user_id]), uses a column which does not belong to the specified table, which is ALLSELECTED['Date']

[amitchandak]

@Quiny_Harl , Try like

Cumulative Total Distinct IDs =
VAR MaxDate = MAX('Date'[date])
RETURN
CALCULATE(Sumx(values('Date'[date]), calculate(DISTINCTCOUNT(table2[user_id]))),FILTER(ALLSELECTED('Date'),'Date'[date]<=MaxDate))

or for each level

Cumulative Total Distinct IDs =
VAR MaxDate = MAX('Date'[date])
RETURN
CALCULATE(Sumx(values('Date'[date]), calculate(DISTINCTCOUNT(table2[user_id]))),FILTER(ALLSELECTED('Date'),'Date'[date]<=MaxDate))

or

Cumulative Total Distinct IDs =
VAR MaxDate = MAX('Date'[date])
RETURN
CALCULATE(Sumx(values('Date'[Year]), calculate(DISTINCTCOUNT(table2[user_id]))),FILTER(ALLSELECTED('Date'),'Date'[date]<=MaxDate))

[Quiny_Harl]

@amitchandak Thank you for your help!
The last one works for the Year level of the date. However, it is necessery for the user to be able to drill down to quarter, month and date level and in this case the measure doesn't give correct results. Any idea?