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