Cumulative Distinct Count by Year

[matthewtjy]

Hi, I've used the measure "Measure = CALCULATE(DISTINCTCOUNT(DATA TO COUNT),FILTER(ALLSELECTED(DATA_TABLE),(DATA DATE)<=MAX(DATA_DATE)))" to plot a cumulative graph.

For example's sake, I've simplied my data as follows:

DATA	YEAR
A	2000
B	2001
B	2001
C	2002
C	2002
C	2002
D	2003
D	2003
D	2003
D	2003

Plotting the above data with the measure, I get a cumulative graph showing the following results: 2000=1; 2001=2; 2003=3; 2004=4.

But when I apply a date filter e.g. last 2 years (2003 and 2004), the results end up showing a graph with 2003=1 and 2004=2, instead of 2003=3 and 2004=4. It's like the graph excluded all data before 2003.

Is there anyway to fix it so that when i set a date range filter or drill down the data, it still includes all data before 2003, but just displays years 2003 and 2004?

Thanks in advance!

[V-pazhen-msft]

@matthewtjy 

Yes, correct😀. Just add other filters with AND (&&).

Paul 

[matthewtjy]

Hi Paul,

Pertaining to this measure, is it possible to have a SUM of 2 measures (Measure1 & Measure 2) and both measures are from 2 different tables and i am unable to combine both tables into 1

e.g. Measure1 = CALCULATE(DISTINCTCOUNT([Data Column]),FILTER(ALL(Table1),[Year Column]<=MAX([Year Column])&& [COLOR]<>"BLUE"&& [MATERIAL]<>"Plastic"))

Measure2 = CALCULATE(DISTINCTCOUNT([Data Column]),FILTER(ALL(Table2),[Year Column]<=MAX([Year Column])&& [COLOR]<>"BLUE"&& [MATERIAL]<>"Plastic"))

[matthewtjy]

Thanks Paul!

[V-pazhen-msft · Accepted solution]

@matthewtjy 

Sorry, had wrong braces. Try:

Measure = CALCULATE(DISTINCTCOUNT([Data Column]),FILTER(ALL(Table),[Year Column]<=MAX([Year Column])&& [COLOR]<>"BLUE"))

Paul 

[matthewtjy]

Thank Paul! This works like a charm!

Just to check, if i need to include extra filters e.g. "Material" is not "Plastic", i could just add on a filter as such correct?

Measure = CALCULATE(DISTINCTCOUNT([Data Column]),FILTER(ALL(Table),[Year Column]<=MAX([Year Column])&& [COLOR]<>"BLUE"&& [MATERIAL]<>"Plastic"))

[V-pazhen-msft]

@matthewtjy 

Try add the color filter inside the running total expression.

Measure = CALCULATE(DISTINCTCOUNT(DATA TO COUNT),FILTER(ALL(DATA_TABLE),[DATA DATE]<=MAX(DATA_DATE) && [COLOR]<>"BLUE"))

Paul Zheng _ Community Support Team

[matthewtjy]

This formula gives the error "The MAX function only accepts a column reference as an argument"

Is there another workaround to this?

Thanks in advance!

[V-pazhen-msft]

@matthewtjy 

Then try add another filter outside the running total expression.

Measure = CALCULATE(DISTINCTCOUNT(DATA TO COUNT),FILTER(ALL(DATA_TABLE),(DATA DATE)<=MAX(DATA_DATE)), FiLTER(DATA_TABLE, [COLOR]<>"BLUE"))

Paul Zheng _ Community Support Team

[matthewtjy]

I've tried the DAX measure you shared but i've ended up without a cumulative graph now like in the attached photo (refer to blue graph line). Did I do something wrong?

[V-pazhen-msft]

@matthewtjy 

If you want to ignore the date filter. Try replace the ALLSELECTED() to ALL(). 

Measure = CALCULATE(DISTINCTCOUNT(DATA TO COUNT),FILTER(ALL(DATA_TABLE),(DATA DATE)<=MAX(DATA_DATE)))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[matthewtjy]

Unfortunately this doesn't work as I have to apply additional filters to the visualisation. Is there a way to include an additional filter to your formula for example to exclude "Blue" in another column called "Colour"?

[matthewtjy]

Hi, hope someone would be able to help out, have included screenshots below for further explanation.

Based on all my data since 2014, the cumulative count for the blue line is 903 as of May 2021 (see first picture).

However, once i filter the results to show the last 13 months, the blue line only shows 227 as of May 2021 (see second picture).

Is there a way to show the cumulative count to count since 2014 even with the silcer to only show last 13 months?

Thanks!