Bring back values to another table based on date values

[rush]

Hi 

I need help trying to bring back values from one table to another based on dates.

I have a table with staff with different effect date values for employment status like someone changing Permanent to contractor etc.
I am needing the values from table 1 in table 2 based on each staff and their effective dates.

Please see the link below to the sample data and my expected outcome.

Sample Data and Expected Outcome 

[danextian]

Hi @rush ,

In Power Query, you can get the value from another table by doing a merge. If there are multiple criteria, you can add a column in each of the table which combines all the needed columns as in your case the effective date and EEID and then use that column to merge. I am however confused with your expected result because, for example, 122 and 02/02/2021 does not exist in Table1 but is in your expected outcome.
Please refer to this pbix for the initial solution. You will find it when you go to the Query Editor. https://drive.google.com/file/d/1bNgZfgo4Fhd0mnJ3BvKR_dCrD1Mqigtz/view?usp=sharing

[rush]

@danextian Thank you for your response. 

So it is not just a match on the date only but it needs to fill it up until there is another effective date.

I can't use power query as I have table 2 created using DAX. 

It needs to fill in the data from employment status based on the effective date. So if there are 2 or more effective dates it will fill the table with those corresponding dates.