Finding DATEDIFF between date employed and last recorded entry

[Anonymous]

I have 2 tables:

• One that shows a list of employees and their hire date
• One that shows a list of actions completed, the date it was completed, and the employee who completed that action

I need to create a column on the first table that shows the time between the employees hire date ('Employees'[HireDate]) and the last time they completed an activity ('Actions'[DateCompleted]).

What DAX expression can I use for this? I've tried using:
 
DATEDIFF('Employees'[HireDate], MAX('Actions'[DateCompleted]), DAY) 

But this only returns the days between their hire date and the last action completed in the 'Actions' table, not the last 'Action' completed by the respective employee.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I assume the two tables does not have any relationship.

I tried to create a sample pbix file like below.

In case there is no relationship between two tables, please try like below.

Please check the below picture and the attached pbix file.

Days between hired and action completed CC =
DATEDIFF (
    Employee[Hire date],
    MAXX (
        FILTER ( 'Action', 'Action'[Employee] = EARLIER ( Employee[Employee] ) ),
        'Action'[Complete date]
    ),
    DAY
)

[Anonymous]

Worked like a charm! Thanks so much!!