Find the most recent value in a column

[ldoriejhl]

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
2021	3	CC
2021	4	DD
2022	1	AA
2022	2	BB
2022	3	CE
2022	4	DE
2023	1	AA
2023	2	BB
2023	3	CE
2023	4	DE

I am trying to figure out how to create a DAX column that would return only the most recent name for each employee ID, i.e. only the items at the end of the table. Any help appreciated on this one!

[Gabry]

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

[123abc · Accepted solution]

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.