Distinct Count based on Most Recent Date

[Anonymous]

Hello, I am trying to find the distinct count for each party type and ID by risk. I deciced to merge the Party Type and ID columns (merged) and calculate the distinct count of this new, merged column, and then use a matrix table with risk as the rows and date for columns. Please see table below as an example. The measure for value is:

Merge Count = DISTINCTCOUNT(DISP_HISTORY[Merged])

My problem now is that if I do distinct counts by month, I have IDs and Party types that may cross months, but I want it to calculate based on the MOST RECENT date. So, in September, the count may be 2 (one for each type), but in November, the September count will now be 0 and the November count will be 2 (one for DIS 8872-D2 and one for SET 8872-D1). 

ID	Risk	Code	Year	Month	Party Type	Merged
8872	No	Dis	2020	Sept	D1	8872-D1
8872	No	Dis	2020	Sept	D2	8872-D2
8872	No	Dis	2020	Nov	D2	8872-D2
8872	No	Set	2020	Nov	D1	8872-D1
		Year	2020	2020
		Risk	September	November	Total
		NO	2	2	2
		DIS	2	1	2
		8872-D1	1	1	1
		8872-D2	1	-	1
		SET	-	1	1
		8872-D1	-	1	1
		TOTAL	2	2	2

[westwrightj · Accepted solution]

Hey @Anonymous ,

    I think I understand what you are trying to do here.

Below is the sample data I am using

The problem you identified makes complete sense, you are looking for distinct counts but want to assign each unique ID-Party Type combination to the most recent month

So first I'll perform a similar combined column as you mentioned

ID_Party Type = 

'Sample Datas'[ID ]&"_"&'Sample Datas'[Party Type]

Have gone ahead in the query editor and changed values for "Sept" to "Sep"

Next we can create a makeshift date column based on what we have available

Date Column = 

"01/" & ('Sample Datas'[Month]) & "/" & 'Sample Datas'[Year]

Then we can change the column type to "Date"

Next we can 'rank' the ID_Party Type combinations by date so the most recent is always ranked first

Ranked_ID Party Type = 

var ID_PartyType = 'Sample Datas'[ID_Party Type]

return

RANKX(
FILTER('Sample Datas', 'Sample Datas'[ID_Party Type] = ID_PartyType),
'Sample Datas'[Date Column],
'Sample Datas'[Date Column], DESC, Dense)

Now we if use either of the below measurements if you want to count unique ID or unique Party-ID combinations

Distinct Count of ID = 

CALCULATE(DISTINCTCOUNT('Sample Datas'[ID ]), 'Sample Datas'[Ranked_ID Party Type] = 1)


Distinct Count of Party-ID = 

CALCULATE(DISTINCTCOUNT('Sample Datas'[ID_Party Type]), 'Sample Datas'[Ranked_ID Party Type] = 1)

In this case you can use the date coulmn we made earlier for the columns.

Let me know if this solution works for you or if we need to tinker a bit more

[Anonymous]

Thank you so much!! This is VERY helpful, and very well written. I really appreciate it!! 

[MFelix]

Hi @Anonymous ,

What is the expected result? Do you want to count only values on the month were they appear or having a count until they change value?