Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rush
Helper V
Helper V

Bring back values to another table based on date values

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 

2 REPLIES 2
danextian
Super User
Super User

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





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.