lookup an employee and return role based on date entry was made

[ALemmy]

Hi gang 

I have been trolling through as many questions as I can and you have been super helpful before so maybe someone can help me again 🙂

I have two tables in a larger data set 

Table 1: 

Has all time entries submitted by all employees (EMPLID) and the date the entry was made (TRANSDATE) 

Table 2: 

Role table with start and end date of roles and promo's 

The EMPLID will be duplicated in the EMPLID column if the person has had a 'role name' change in the system - each change is time stamped, the start (CREATED) and end date (Latest date) of the role are next to each occurance of the EMPLID in table 2.

*if the role hasnt been edited it will return todays date in latest date

What I need to do is look up the EMPLID in table 1 and TRANSDATE table 1 and match it to the correct role NAME for that EMPLID in table 2. 

I used to have a lookup formula that worked fine when I didnt have EMPLID duplications in table 2 so now I need to implement this change to keep track of changing roles in a project and implement the right rate throughout the life of it. 

If anyone can give me any pointers it would really help 🙂 

Thank you all and HAPPY NEW YEAR 

Anne

[parry2k]

@ALemmy Appreciate the feedback and glad it worked out. I would like to say, you explained your problem very well, and it was fun to work with. Cheers!!

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@ALemmy try this as a new column in your task table, change col and table name as per your model.

Role = 
VAR __taskDate = Tasks[TaskDate]
VAR __empId = Tasks[EmpId]
RETURN
CALCULATE ( 
    LASTNONBLANK ( Emp[Role], TRUE() ), 
    __empId = Emp[EmpId],
    __taskDate >= Emp[Start],
    __taskDate <= Emp[End]
) 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[ALemmy]

THANK YOU SO MUCH ... honestly and your break down is super helpful to understand 

be blessed 

Anne