Find the Latest Value

[cevangelista]

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! 🙂

[Sean]

DELETED!

[cevangelista]

Thanks @Sean

It works except the date is all 31-Dec-18, did I type in something wrong?

[Sean]

Can you post the measure formula you are using? Are you referencing the right date and in the right table?

[cevangelista]

Latest Recruitment Stage Date = CALCULATE (lastdate('MMW Recruitment (2)'[Recruitment Workflow Stage Date].[Date]), VALUES('MMW Recruitment (2)'[Candidate - FT ID No.]))

[Sean]

DELETED!

[cevangelista]

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?

[TomMartens · Accepted 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 

[cevangelista]

Thank you @TomMartens!