Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Thank you I realise my ranking is incorrect, so your solution did not work, I will keep trying!
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.
@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
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |