Tracking role changes (LOOKUPVALUE?)

[st-mb]

Hello,

I'm trying to create a report that tracks employees' tasks they've completed and the role they completed it as.

For example, all employees have a role at the beginning of this year, but some may be promoted. These changes are tracked in the Employees table:

UserID	Name	        Role	        Date
1	EmployeeA	Support	        1/1/2018
2	EmployeeB	Support	        1/1/2018
3	EmployeeC	Support	        1/1/2018
4	EmployeeD	Support	        1/1/2018
5	EmployeeE	Senior Support	1/1/2018
3	EmployeeC	Senior Support	3/18/2018
5	EmployeeE	Supervisor	6/4/2018

The tasks they've completed over the year so far are in the Tasks table:

TaskName	Date	        UserID
Task A	        2/1/2018	2
Task B	        2/1/2018	1
Task C	        3/1/2018	3
Task D	        3/25/2018	3
Task E	        5/5/2018	4
Task F	        6/3/2018	5
Task G	        6/4/2018	5

What I was thinking the output would be is similar to this:

TaskName	Date	          UserID   Role
Task A	        2/1/2018	  2	   Support
Task B	        2/1/2018	  1	   Support
Task C	        3/1/2018	  3	   Support
Task D	        3/25/2018         3	   Senior Support
Task E	        5/5/2018	  4	   Support
Task F	        6/3/2018	  5	   Senior Support
Task G	        6/4/2018	  5	   Supervisor

I can sort of achieve this with LOOKUPVALUE and UserID, but I'm having difficulties adding the date part (unless there's a more efficient way to go about doing this?).

Thanks in advance!

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

=LOOKUPVALUE(Employees[Role],Employees[UserID],[UserID],Employees[Date],CALCULATE(MAX(Employees[Date]),FILTER(Employees,Employees[UserID]=EARLIER([UserID])&&Employees[Date]<=EARLIER(Tasks[Date]))))

Hope this helps.

[st-mb]

Thank you both! I used Ashish_Mathur's suggested formula and it worked perfectly.

[Anonymous]

Hey @st-mb

A couple of steps to calculate this. First, I create a table of unique UserID's to connect the Employees and Tasks table.

Then you can calculate the new role column with the following formula:

Role = 
VAR CurrentUser = FIRSTNONBLANK(Tasks[UserID],1)
RETURN
CALCULATE(
    FIRSTNONBLANK(Employees[Role],1),
    TOPN(
        1,
        FILTER(
            Employees,
            Employees[UserID] = CurrentUser && Tasks[Date] >= Employees[Date]
        ),
        Employees[Date]
    )
)

This gives you the desired output, shown below:

Let me know if you have any questions on the formula.

Hope this helps!

Parker