Distinct Count based on Most Recent Date

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