Forum Discussion
Find the Latest Value
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! :)
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
8 Replies
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
Thank you TomMartens!
DELETED!
Can you post the measure formula you are using? Are you referencing the right date and in the right table?
