lookup value within date range

[Anonymous]

Scenario

Adding a column with Role to the timesheets table. 

Each employee has a role, which will change over time. I want to look up the role of the employee based on the date of the timesheet. The right role can be found by looking at the date of the timesheet. this date should be in the date range of the employee role table. 

Question

Which expression is needed?

Timesheets

Employee	Date
John	1-3-2019
John	1-3-2020
Chris	1-8-2019
Chris	1-9-2019

Employee Role

Employee	Role	Start Date	End Date
John	Medior staff	1-1-2019	31-12-2019
John	Senior staff	1-1-2020	31-12-2020
Chris	Junior staff	1-6-2019	31-12-2019
Christ	Medior Staff	1-1-2020	31-12-2019

[Anonymous]

Thanks you both!

Allison is the winner 🙂 

I have made a little adjustment to also include results which are on the start or end date. This was not clearly mentioned in my first post. < and > are replaced for <= and >=.

Variable  =

CALCULATE (

    SELECTEDVALUE ( EmployeeRole[Role] ),

    FILTER (

        EmployeeRole,

        EmployeeRole[Start Date] <= SELECTEDVALUE(Timesheets[Date]

            && EmployeeRole[End Date] => SELECTEDVALUE(Timesheets[Date]

    )

)

[amitchandak]

@Anonymous , A new column in timesheet to get role

new column =
maxx(filter('Employee Role', 'Employee Role'[Start Date] <=Timesheets[Date] && 'Employee Role'[End Date] >=Timesheets[Date]
&& 'Employee Role'[Employee] =Timesheets[Employee]),'Employee Role'[Role])

[AllisonKennedy · Accepted solution]

@Anonymous  You're looking for an approximate lookup. See if this post helps: 

https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html

Use the 'bonus between' measure and edit for your data: 

Bonus Between =

CALCULATE (

    SELECTEDVALUE ( EmployeeRole[Role] ),

    FILTER (

        EmployeeRole,

        EmployeeRole[Start Date] < SELECTEDVALUE(Timesheets[Date]

            && EmployeeRole[End Date] > SELECTEDVALUE(Timesheets[Date]

    )

)