Forum Discussion

GerbenvdHazel's avatar
GerbenvdHazel
Helper I
6 years ago

Look up value between two dates from different table

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....
Need Help
Tips and Tricks
GerbenvdHazel's avatar
GerbenvdHazel
Helper I
6 years ago

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's avatar
v-deddai1-msft
Community Support
6 years ago

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's avatar
    GerbenvdHazel
    Helper I
    6 years ago

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

     

    Regards, Gerben

    • v-deddai1-msft's avatar
      v-deddai1-msft
      Community Support
      6 years ago

      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's avatar
        GerbenvdHazel
        Helper I
        6 years ago

        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.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      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.