Lookup value based on date between a range

[RoseyAlexa]

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.

[JarroVGIT]

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! 🙂

[RoseyAlexa]

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

[JarroVGIT]

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! 🙂

[RoseyAlexa]

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.

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.

Must be something in the relationships I think.
Dont judge me on my table structures. I'm new at this stuff

click here for table relationship image

[JarroVGIT]

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.

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! 🙂

[RoseyAlexa]

@JarroVGITthanks for helping.

Here is what I translated your code into:

Timesheet Branch =

VAR staffID = 'Timesheets'[Staff_ID]

VAR timeSheetDate = 'Timesheets'[Timesheet Date]

RETURN

CALCULATE(SELECTEDVALUE('Career History'[Branch], "Multi"), FILTER('Career History', 'Career History'[Staff_ID] = staffID && 'Career History'[Career History Start Date] <= timeSheetDate && OR('Career History'[Career History End Date] = BLANK(), 'Career History'[Career History End Date]> timeSheetDate)))

[JarroVGIT]

That seems to be correct. Is it possible to share your PBIX? You can PM me the link and I will have a look, I think the datamodel is different then that I currently think.