Forum Discussion

neek05's avatar
neek05
Frequent Visitor
3 years ago
Solved

Return group with max date

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/2...
Need Help
  • vicky_'s avatar
    vicky_
    3 years ago

    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.

vicky_'s avatar
vicky_
Super User
3 years ago

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.