How to determine most recent entry per an employee (max date in a moment of time)

[smb711]

• How can I create a DAX measure to show the most recent date entry per an employee?
• So to see only the 'MAX' most recent entry per an employee number
• I need to aggregate all the multiple record per employee to only keep the max record, most recent entry

For example, for Joe Blogs - I only want to see the record starting - 20/10/2020

For example, for Sam Bills - I only want to see the record starting - 19/10/2019

Employee number	Name	Position start date	Position end date	Employment Basis
592342	Joe Bloggs	1/04/2012	1/05/2013	Contract
592342	Joe Bloggs	2/05/2013	3/05/2015	Part-time
592342	Joe Bloggs	4/05/2015	15/09/2019	Full-time
592342	Joe Bloggs	16/09/2019	19/10/2020	Part-time
592342	Joe Bloggs	20/10/2020		Full-time
347977	Sam Bills	19/10/2019	25/12/2020	Part-time
347977	Sam Bills	26/12/2020	15/01/2021	Casual
347977	Sam Bills	16/01/2021	31/12/2021	Full-time

[V-lianl-msft · Accepted solution]

Do you want to create a calculated column? Then create a relationship with a table?

You need to create this column in the table with employee name, or create a calculated table.

Table 2 =
VAR t =
    SELECTCOLUMNS (
        'Table',
        "emp.no", 'Table'[Employee number],
        "emp.name", 'Table'[Name],
        "psd", 'Table'[Position start date],
        "lastest_date",
            CALCULATE (
                MAX ( 'Table'[Position start date] ),
                ALLEXCEPT ( 'Table', 'Table'[Name] )
            )
    )
RETURN
    FILTER ( t, [psd] = [lastest_date] )

[VahidDM]

Hi @smb711 

Is that 19/10/2019 for Sam Bills correct?

BTW, try this measure:

Most Recent Date =
CALCULATE (
    MAX ( table[Position start date] ),
    ALLEXCEPT ( table, table[Name] )
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[smb711]

Thanks! How do I apply this to aggregate/filter down the number of options in the table? I am unable to create a relationship between this table and another table with multiple employee records even though as a result of the measure it has filtered down the multiple records to just the one most recent? 

"You can't create a relationship between these two columns because one of the columns must have unique values"

Do you have a suggested workaround, i.e. complete the calculation in the power query editor as a custom column and then filter (If so how?) 

I am trying to get information from another table with multiple records by forging a relationship one to many relationships with another table with multiple records per employee number. 

Apologies, for Sam Bills it would be 16/01/2021

To clarify, I only want to display the most current date record per an employee number 

Most recent date =
CALCULATE(
MAX(PBIPND_vwClassRank[StartDate]),
ALLEXCEPT('Date Table', 'Date Table'[Date])
)