Return group with max date

[neek05]

Hi, I have a table like the one below:

USERID	DATE	GROUP
User1	20/01/2023	GroupA
User1	21/01/2023	GroupA
User1	22/01/2023	GroupB
User1	23/01/2023	GroupB
User2	18/01/2023	GroupC
User2	20/01/2023	GroupD
User2	21/01/2023	GroupD
User2	22/01/2023	GroupC

And I want to get the group that holds the value with the max date,  so I should have a table like this: 

USERID	DATE	GROUP
User1	22/01/2023	GroupB
User1	23/01/2023	GroupB
User2	18/01/2023	GroupC
User2	22/01/2023	GroupC

Just to clarify, I don't need the rows with the max date by group, I need the rows with the group that has the max date. Does anyone know of a way I could accomplish this? Ideally, I would like to get a calculated column in Dax or a measure to use as a filter in a table visual (power query isn't going to work for me). Thanks in advance!

[vicky_ · Accepted solution]

I think there's two parts to the formula - find which group has the max date (var groupWithMaxDate), and then check if the existing group is equal to that group (the return IF condition). 

Column = 
var groupWithMaxDate = CALCULATE(MAX('Table'[GROUP]), 
    FILTER('Table', 'Table'[DATE] = CALCULATE(MAX('Table'[DATE]), ALLEXCEPT('Table', 'Table'[USERID]))
    && 'Table'[USERID] = EARLIER('Table'[USERID])
))
return IF('Table'[GROUP] = groupWithMaxDate, 'Table'[GROUP])

Hopefully this helps.