MAX of DATE in LOOKUP

[Anonymous]

Hey I have 2 tables:  Hires, Terms,

HIRES:

ID  HIRE DATE

1    8/17

1    8/25

2    9/1

2    9/31

TERMS:

ID       TERM_DATE

1         8/20

1         8/31

2        9/10

2        10/3

I want to do a look up by bringing the hire_date into the TERMS table by checking the "ID". However, since these ID's have 2 hire dates but same ID again, I woud like to consider only the max hire date of the person so that he is counted only once instead of twice.

Is there a way this can be done?

Thanks!

[PattemManohar]

@Anonymous Please try this...

HireDate = CALCULATE(MAX(Hires[HireDate]),FILTER(Hires,Hires[ID]=EARLIER(Terms[ID]))) 

Here, I've assumed you have a proper date in HireDate field in Hires table. If that is not the case, the data contains only MM/DD format as mentioned then the logic will be different as it will be in text format..

[Anonymous]

@PattemManohar Hey, I think that earlier[id] does not work. The other formula worked correctly.

Thanks!

[PattemManohar]

@Anonymous Not sure why it didn't work for you. I've also attached the screenshot of the same.

Anyway, you got it worked !! That's cool !!

[parry2k · Accepted solution]

@Anonymous try following, change the table name as per your model.

HireDate = CALCULATE( MAX( Table1[HireDate] ), FILTER( Table1, Table1[Id] = Table2[Id] ) )

[Anonymous]

@parry2k Thanks!