Forum Discussion
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 |
| 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!
Try this DAX code for the calculated column:
DAXMostRecentName = 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.
2 Replies
- 123abc
Community Champion
Try this DAX code for the calculated column:
DAXMostRecentName = 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.
