Forum Discussion

st-mb's avatar
st-mb
Frequent Visitor
8 years ago
Solved

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, bu...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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