How to search the last row of the group and put the text or number?

[saranp780]

I have some data like this.

The room (number)	Year	Grade
102	2016	2.07
102	2017	3
102	2018	2.96
103	2016	2.96
103	2015	3.12
103	2016	3.12
103	2017	3.12
104	2017	1.69
104	2018	1.69
105	2014	2.41
105	2015	3.96
105	2017	3.24

My requirement is to search the last row of every room number to put some remark in a new column.

Example

Room(number)	Year	Grade	remark
102	2016	2.07
102	2017	3
102	2018	2.96	Last year of this room
103	2016	2.96
103	2015	3.12
103	2016	3.12
103	2017	3.12	Last year of this room
104	2017	1.69
104	2018	1.69	Last year of this room
105	2014	2.41
105	2015	3.96
105	2017	3.24	Last year of this room

The new column is to remark the last year of the room. So now, what do DAX or measure I have to use?

Thank you so much for giving help.

[ValentinBIA · Accepted solution]

Hi @saranp780,

In order to ger what you are looking for, you should:

- Go to the Power Query Editor

- Duplicate your Query

- On the duplicated query, group by room number, with the operation max, on the column Year

- Add a custom column (= "Last year of this room")

- Go back to your main Query

- Merge with the duplicated query using the "the room number" as a 1st matching column and "year" as the 2nd matching column (hold control or shift when selecting to have more than one)

- Expand with only the custom column

- There you go !

Hope that helps !

Best,

Valentin