cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## MAX of DATE in LOOKUP

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!

1 ACCEPTED SOLUTION
Super User

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

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

5 REPLIES 5
Community Champion

`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..

Proud to be a PBI Community Champion

Anonymous
Not applicable

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

Thanks!

Community Champion
@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 !!

Proud to be a PBI Community Champion

Super User

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

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k Thanks!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors