IF row meets conditions then return value from a previous row.
02-21-202102:32 PM
Hello,
I currently have data that looks like this:
Date/Time
Agent Name
Phone Number
Running Count
Department
IsFirst
FirstDepartment
1/1/2021 3:00 PM
Agent A
1234567890
1
Department X
Department X
1/2/2021 4:00 PM
Agent B
0987654321
1
Department Y
Department Y
1/3/2021 8:00 AM
Agent C
1234567890
2
Department Y
Duplicate
Department X
1/10/2021 10:00 AM
Agent D
0987654321
2
Department X
Duplicate
Department Y
1/13/2021 5:00 PM
Agent E
5678901234
1
Department X
Department X
What I want to do is if "IsFirst" = Duplicate, then I want to find when that phone number first occured and return that department that first took the call. My "FirstDepartment" is the column I am trying to make. However, I have been unable to obtain those desired results.
You don't really need the "Duplicate" check but you can add that if you want.
First Dept :=
var d = SELECTEDVALUE(Calls[Date/Time])
var pd = CALCULATE(max(Calls[Date/Time]),ALLEXCEPT(Calls,Calls[Phone Number]),Calls[Date/Time]<d)
var pdept = CALCULATE(max(Calls[Department]),ALLEXCEPT(Calls,Calls[Phone Number]),Calls[Date/Time]=pd)
return pdept
