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. 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

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

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    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])

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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