Look up value between two dates from different table

[GerbenvdHazel]

Good day,

I have two tables.

One with trajectories with an end date.

The second table concerns relationships where persons working with a start and end date. The dates are not match exactly. Both tables have the same personal number.

I would like to have the company name from the second table.

A person can have different trajectories and different companies (history). I thougth about a between formula with datefilter and filter personal number. The end dates may differ.

link to file 

Hope to hear the solution
Thanks!

[GerbenvdHazel]

A person can have different trajectories and different companies (history). I thougth about a between formula with datefilter and filter personal number. The end dates may differ.

link to file 

[RichardJ]

Hi @GerbenvdHazel ,

Depending on your data, the LOOKUP function would let you use the Personal Number from the first table to lookup the Personal Number on the Second table then return the value of the Company from the matching row on the second table to the first table.

https://www.wallstreetmojo.com/power-bi-lookupvalue/

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

explains how it works.

Hopefully this helps answer your question,

Cheers,

Richard

[parry2k]

@GerbenvdHazel is the 2nd table on 1 side of the relationship, if yes, then you can add column in 1st table like this

Company = RELATED ( Table2[Company] )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[GerbenvdHazel]

A person can have different trajectories and different companies (history). I thougth about a between formula with datefilter and filter personal number

[GerbenvdHazel]

A person can have different trajectories and different companies (history). I thougth about a between formula with datefilter and filter personal number

[v-deddai1-msft]

Hi @GerbenvdHazel ,

You can add filter in LOOKUPVALUE function. But without sample data, it's difficult to provide a detailed formula.

Would you please inform us more detailed information( your  data(by OneDrive for Business)) if possible? Then we will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

[GerbenvdHazel]

Thanks for helping. 
I edit the post with a link to the file.

Regards, Gerben

[Syndicate_Admin]

Hello

I have a question that I do not know how to solve. I have an excel with information of: several vehicles of a company (column with registrations) with an entry date (column "phase in") and another column of output "phase out". I also have another column "Ownership" that has to have the filter "Owned" which is the data that I want to know happens to know the fleet owned by the company.

I wanted to know what fleet I own every month in fiscal year. I understand that with a "DAX" you could? with which exactly??

I have the excel but I can't attach it, if necessary I can send it.

Thanks in advance.

[v-deddai1-msft]

Hi @GerbenvdHazel ,

So what is the filter that you get companyName, I see you have duplicate Unique personal number value

and duplicate Date start value in  trajectories table.

Best Regards,

Dedmon Dai

[GerbenvdHazel]

What I'm looking for is a lookupvalue that filters first on Unique personal number and then within the range of dates.

So end date of the trajectories and then search between date start and end of the placements. And come up with the companyname.

I hope it is possible.

[GerbenvdHazel]

A person can have different trajectories and different companies (history). I thougth about a between formula with datefilter and filter personal number