Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
How can I find the latest value (Recruitment Stage) for each ID number in the table below based on the Recruitment Stage date?
ID No | Recruitment Stage | Recruitment Stage Date |
1 | LOO Issued | 14/05/2018 |
1 | LOO Accepted | 15/05/2018 |
4 | Medical Booked | 3/04/2018 |
3 | Medical Booked | 1/05/2018 |
3 | Medical Approved | 5/05/2018 |
5 | Medical Booked | 30/04/2018 |
5 | Medical Approved | 10/05/2018 |
5 | LOO Issued | 12/05/2018 |
5 | LOO Approved | 13/05/2018 |
7 | Phone Screen | 9/05/2018 |
7 | Medical Booked | 12/05/2018 |
11 | Site Referral | 14/05/2018 |
2 | Phone Screen | 2/05/2018 |
2 | Reference Checking | 9/05/2018 |
2 | Medical Booked | 12/05/2018 |
I would need the data to show this:
ID No | Recruitment Stage | Recruitment Stage Date |
1 | LOO Accepted | 15/05/2018 |
4 | Medical Booked | 3/04/2018 |
3 | Medical Approved | 5/05/2018 |
5 | LOO approved | 13/05/2018 |
Thanks in advanced! 🙂
Solved! Go to Solution.
Hey,
i created a calculated column using this DAX statement:
Is Latest = var currentIDNo = 'Table1'[ID No] var latestDate = CALCULATE( MAX('Table1'[Recruitment Stage Date]) ,FILTER(ALL('Table1') ,'Table1'[ID No] = currentIDNo ) ) return IF('Table1'[Recruitment Stage Date] = latestDate, TRUE(), FALSE())
Here is a screenshot of the result (my assumptuon: the Recruitment Stage Date is of data type datetime or date). Please be aware that my table shows other dates, this is due some date formatting issues 🙂 but nevertheless I can use the statement on your table:
This column can now be used to filter the table.
Hopefully this is what you are looking for.
Regards
Tom
DELETED!
Can you post the measure formula you are using? Are you referencing the right date and in the right table?
Latest Recruitment Stage Date = CALCULATE (lastdate('MMW Recruitment (2)'[Recruitment Workflow Stage Date].[Date]), VALUES('MMW Recruitment (2)'[Candidate - FT ID No.]))
DELETED!
It works! Thank you so much.
If we wanted to use it in an axis for a chart, I can't use the measure can I?
Hey,
i created a calculated column using this DAX statement:
Is Latest = var currentIDNo = 'Table1'[ID No] var latestDate = CALCULATE( MAX('Table1'[Recruitment Stage Date]) ,FILTER(ALL('Table1') ,'Table1'[ID No] = currentIDNo ) ) return IF('Table1'[Recruitment Stage Date] = latestDate, TRUE(), FALSE())
Here is a screenshot of the result (my assumptuon: the Recruitment Stage Date is of data type datetime or date). Please be aware that my table shows other dates, this is due some date formatting issues 🙂 but nevertheless I can use the statement on your table:
This column can now be used to filter the table.
Hopefully this is what you are looking for.
Regards
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |