IF row meets conditions then return value from a previous row.

[jl02]

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.

[lbendlin]

Can you guarantee that your Date/Time column has unique values and can be used for the sorting? 

[jl02]

Yes, They go down to the micro-second.

[lbendlin]

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