topic Return value based on matching value & Date difference in DAX Commands and Tips

Return value based on matching value & Date difference

Anonymous — Fri, 13 Dec 2019 22:54:19 GMT

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] ) )

Re: Return value based on matching value & Date difference

az38 — Sat, 14 Dec 2019 08:12:36 GMT

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

Re: Return value based on matching value & Date difference

Anonymous — Mon, 16 Dec 2019 22:00:08 GMT

Hi az38

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

Re: Return value based on matching value & Date difference

bwarner87 — Mon, 08 Nov 2021 20:59:02 GMT

this worked like a charm for me! thank you!