Forum Discussion
Lookup value based on date between a range
I have two tables:
Timesheets - has staff hours and the date they worked those hours
Career History - has a Start Date and End Date that an employee worked at a particular Branch
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.
For Example, Fred moves moves from the Sheffield branch to the London branch on 01/01/2020:
current Timesheet table:
| Staff_ID | Name | TimesheetDate | Hours |
| 030 | Fred Jones | 23/12/2019 | 7.5 |
| 030 | Fred Jones | 03/01/2020 | 9 |
current Career History table (end date is blank if they still work at that branch):
| Staff_ID | Name | StartDate | EndDate | Branch |
| 030 | Fred Jones | 01/07/2019 | 31/12/2019 | Sheffield |
| 030 | Fred Jones | 01/01/2020 | London |
Result I want in the Timesheet table:
| Staff_ID | Name | TimesheetDate | Hours | Branch |
| 030 | Fred Jones | 23/12/2019 | 7.5 | Sheffield |
| 030 | Fred Jones | 03/01/2020 | 9 | London |
Please help me by giving the syntax for the calculated column.
7 Replies
Hi RoseyAlexa ,
This should meet your requirements.
Branch = VAR staffID = Timesheet[Staff_ID] VAR timeSheetDate = Timesheet[TimesheetDate] RETURN CALCULATE(SELECTEDVALUE(Careerhistory[Branch], "Multi"), FILTER(Careerhistory, Careerhistory[Staff_ID] = staffID && Careerhistory[StartDate] <= timeSheetDate && OR(Careerhistory[EndDate] = BLANK(), Careerhistory[EndDate] > timeSheetDate)))Result:
Kind regards
Djerro123
-------------------------------
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! 🙂
Nearly right, but it returns a value of Multi for nearly every row.
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?
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)
https://1drv.ms/u/s!Ancq8HFZYL_aiIoUNv6XLc0V5ONqyg?e=kRTFV9
Kind regards
Djerro123
-------------------------------
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! 🙂
