Forum Discussion
Tracking role changes (LOOKUPVALUE?)
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!
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.
3 Replies
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
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.
Thank you both! I used Ashish_Mathur's suggested formula and it worked perfectly.
