Find most recent appointments of a certain type

[Elisa112]

Hi All

I am trying find the most recent meetings for a certain type of  appointment. For this KPI I need to find the number of days between each appointment. I have managed to rank appointments for each user but now I am stuck on how to find only certain types of meetings.  Here is a sample of the data 

User	meeting ID	meeting type	meeting date	rank
11	12	Personal	01/02/2024	1
11	13	Personal	03/02/2024	2
11	32	Personal	04/02/2024	3
11	10	Combined	31/01/2024	2
11	30	Combined	30/01/2024	1
11	15	Assessment	28/01/2024	1
9	4	Personal	15/01/2024	2
9	6	Personal	13/01/2024	1
9	22	Combined	01/02/2024	1
18	64	Assessment	25/01/2024	1
18	19	Personal	27/01/2024	2
18	25	Personal	13/01/2024	1

I am only looking for the 3  most recent meetings of type personal only , so the output should look like this

User	1st Most Recent	2nd Most Recent	Days diff	3rd Most Recent	Days diff
11	04/02/2024	03/02/2024	1	01/02/2024	02/01/1900
9	13/01/2024	15/01/2024	2

thank you in advance

[Elisa112]

Thank you I realise my ranking is incorrect, so your solution did not work, I will keep trying!

[Elisa112]

Hi @amitchandak 

Thank you, the output I am getting is this

I used the bottom measure to find the most recent 3 meetings, which works well for some users and not others, i.e although all users have 3 meeeting or more the measure only pulls 3 in some cases.  Also the date format includes time, and I do not understand why.

Thanks in advance if you have any suggestions to resolve.

[amitchandak]

@Elisa112 , For the third  most recent you can use the index function , and for last date you can use offset to get diff

Bottom = CALCULATE(Max('Table'[meeting date]) KEEPFILTERS(INDEX(3, ALLSELECTED('Table'[User], 'Table'[meeting ID], 'Table'[meeting Date]),
ORDERBY([meeting Date], desc),, PARTITIONBY('Table'[User]) )))

Last meeting date = CALCULATE(Max('Table'[meeting date]) KEEPFILTERS(offset(-1, ALLSELECTED('Table'[User], 'Table'[meeting ID], 'Table'[meeting Date]),
ORDERBY([meeting Date], ASC),, PARTITIONBY('Table'[User]) )))

Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U