Lookup value based on date between a range

Question

I have two tables:Timesheets - has staff hours and the date they worked those hoursCareer History - has a Start Date and End Date that an employee worked at a particular Branch&nbsp;In the Timesheets table, what is the Syntax for adding a CALCULATED COLUMN which tells me their branch, on the day they recorded their hours.&nbsp;For Example, Fred moves moves from the Sheffield branch to the London branch on 01/01/2020:&nbsp;current Timesheet table:Staff_IDNameTimesheetDateHours030Fred Jones23/12/20197.5030Fred Jones03/01/20209&nbsp;current Career History table (end date is blank if they still work at that branch):Staff_IDNameStartDateEndDateBranch030Fred Jones01/07/201931/12/2019Sheffield030Fred Jones01/01/2020&nbsp;London&nbsp;Result I want in the Timesheet table:Staff_IDNameTimesheetDateHoursBranch030Fred Jones23/12/20197.5Sheffield030Fred Jones03/01/20209London&nbsp;Please help me by giving the syntax for the calculated column.

roseyalexa · Answer

Nearly right, but it returns a value of Multi for nearly every row.

jarrovgit · Answer

Hi&nbsp;RoseyAlexa ,This should meet your requirements.&nbsp;Branch = 
VAR staffID = Timesheet[Staff_ID]
VAR timeSheetDate = Timesheet[TimesheetDate]
RETURN
CALCULATE(SELECTEDVALUE(Careerhistory[Branch], "Multi"), FILTER(Careerhistory, Careerhistory[Staff_ID] = staffID &amp;&amp; Careerhistory[StartDate] &lt;= timeSheetDate &amp;&amp; OR(Careerhistory[EndDate] = BLANK(), Careerhistory[EndDate] &gt; timeSheetDate)))Result:&nbsp;Kind regardsDjerro123-------------------------------If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.Keep those thumbs up coming! 🙂

jarrovgit · Answer

Please find my PBIX here where this solution fits your requirements applied to the testdata your provided. If the solution has a different outcome in your real dataset, then the difference between the your dataset and the dataset you provided to us are likely the cause of the malfunctioning. Without knowing what your real dataset looks like, there is very little I can do?&nbsp;The only thing I can think of: are your date columns of the type Date? (They weren't when I copied your test data into PBI)&nbsp;https://1drv.ms/u/s!Ancq8HFZYL_aiIoUNv6XLc0V5ONqyg?e=kRTFV9&nbsp;&nbsp;Kind regardsDjerro123-------------------------------If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.Keep those thumbs up coming! 🙂

roseyalexa · Answer

The real dataset is much larger. There are multiple entries for any one person on any one day, as they will have put time to a number of projects.The tables I provided were just example tables created in the forum post.I've checked and, yes, they are date fields.&nbsp;I've tried adding other data, to replicate it a little closer to the large dataset, and creating the same relationships as my main dataset.But your PBIX is still working, and my main dataset is not.&nbsp;Must be something in the relationships I think.Dont judge me on my table structures. I'm new at this stuffclick here for table relationship image

jarrovgit · Answer

Hi RoseyAlexa ,First off: no judgement from my side! 🙂 Everybody had to start at some point in PowerBI and most of us here are having fun helping people out with their specific cases, I learn a lot by helping others myself for example 🙂Back to your case; thanks for sharing the model! That really clarifies a few things for me. Could you please share the DAX of the calculated column you used? There might be a litle mistake when translating my solution to your specific column- and table names.&nbsp;Kind regardsDjerro123-------------------------------If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.Keep those thumbs up coming! 🙂

Forum Discussion

Related content

Lookup rank value?

Lookup value

Lookup help

lookup value in another column

Lookup previous month value