Forum Discussion
Return value based on matching value & Date difference
Hello,
Having an issue figuring out how to gather information from a seperate table comparing two different columns. Below are examples of tables. Case Table holds all cases with the date the case was made and the email of who made it. While the Department table has the dates the employee was hired/changed departments (Job Date), the employee's email, and the current department the employee is in.
Case Table
| Case | Date Created | Department | |
| 1 | 7/13/19 | [email protected] | |
| 2 | 1/5/18 | [email protected] | |
| 3 | 10/10/19 | [email protected] |
Department Table
| Job Date | Employee Email | Current Department |
| 5/5/18 | [email protected] | Customer Service |
| 7/10/19 | [email protected] | Operations |
| 10/9/19 | [email protected] | Finance |
| 1/1/18 | [email protected] | Customer Service |
I am trying to get the Current Department the employee was in at the time the case was created. Below is example of what completed case table would look like
Completed Case Table
| Case | Date Created | Department | |
| 1 | 7/13/19 | [email protected] | Customer Service |
| 2 | 1/5/18 | [email protected] | Customer Service |
| 3 | 10/10/19 | [email protected] | Finance |
I had tried using FirstNONBLANK, but cannot work a filter to compare the dates and pull the correct department.
Department =
CALCULATE(
FIRSTNONBLANK(DepartmentTable[CurrentDepartment], 1),
FILTER(
DepartmentTable,
DepartmentTable[Employee Email] = CaseTable[Email]
&& AssociateLog[ModifiedDate] <= Complaints[Date]
)
)
Hi Anonymous
try this calculated column
Department = lookupvalue(DepartmentTable[Current Department];DepartmentTable[Employee Email];[Email];DepartmentTable[Job Date];calculate(max(DepartmentTable[Job Date]);filter(all(DepartmentTable);DepartmentTable[Employee Email]=[Email] && DepartmentTable[Job Date]<=[Date Created])))do not hesitate to give a kudo to useful posts and mark solutions as solution
3 Replies
Hi Anonymous
try this calculated column
Department = lookupvalue(DepartmentTable[Current Department];DepartmentTable[Employee Email];[Email];DepartmentTable[Job Date];calculate(max(DepartmentTable[Job Date]);filter(all(DepartmentTable);DepartmentTable[Employee Email]=[Email] && DepartmentTable[Job Date]<=[Date Created])))do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi az38
That worked perfect! Originally tried using Lookupvalue, but had no luck. Thank you!
this worked like a charm for me! thank you!
