Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

lookup value within date range

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....
Data Protection
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    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]
        )
    )
amitchandak's avatar
amitchandak
Super User
5 years ago

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])