Return value based on matching value & Date difference

[Anonymous]

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	Email	Department
1	7/13/19	John@job.com
2	1/5/18	Joe@job.com
3	10/10/19	John@job.com

Department Table

Job Date	Employee Email	Current Department
5/5/18	John@job.com	Customer Service
7/10/19	John@job.com	Operations
10/9/19	John@job.com	Finance
1/1/18	Joe@job.com	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	Email	Department
1	7/13/19	John@job.com	Customer Service
2	1/5/18	Joe@job.com	Customer Service
3	10/10/19	John@job.com	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]
    )
)

[bwarner87]

this worked like a charm for me! thank you!

[az38 · Accepted solution]

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

[Anonymous]

Hi @az38 

That worked perfect! Originally tried using Lookupvalue, but had no luck. Thank you!