The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
137 | |
106 | |
105 | |
73 | |
59 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
88 |