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.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
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.

 

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

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