Forum Discussion

ldoriejhl's avatar
ldoriejhl
Frequent Visitor
2 years ago
Solved

Find the most recent value in a column

I'm working through a payroll file which features employee ID and name, and it includes instances where an employee name has changed, for example: Year ID Name 2021 1 AA 2021 2 BB...
Need Help
  • 123abc's avatar
    123abc
    2 years ago

    Try this DAX code for the calculated column:

     

    DAX
    MostRecentName = VAR LatestYear = CALCULATE( MAX(Table[Year]), ALLEXCEPT(Table, Table[ID]) ) RETURN CALCULATE( MAX(Table[Name]), Table[Year] = LatestYear, ALLEXCEPT(Table, Table[ID]) )
     
     
    This will create a column called MostRecentName, which will display the most recent name for each employee based on the year. If you apply this to your data, it will show "CE" for employee ID 3 and "DE" for employee ID 4, based on the latest years in your table.
Gabry's avatar
Gabry
Icon for Super User rankSuper User
2 years ago

Hello ldoriejhl ,

 

this is your formula:

 

Last Name =
Var _id = 'Table'[ID]
var _lyear= CALCULATE(MAX('Table'[Year]), 'Table'[ID]=_id, ALL('Table'))
return
CALCULATE(MAX('Table'[Name]), 'Table'[Year]=_lyear, 'Table'[ID]=_id, ALL('Table'))

Let me know if it works for you