To show previous value based another column in DAX

[Dimi_2207]

Hello !

I have a big set of data and need to show in the column "Previous agent" the agent who took the previous call from the same phone number, please see the example below:

Date	Phone number	Agent	Call Attempt	Previous Agent
13-12-23 16:02	11111	Agent 1	1
14-12-23 16:03	11111	Agent 2	2	Agent 1
27-12-23 13:49	11111	Agent 3	3	Agent 2
28-12-23 13:18	22222	Agent 3	1
29-12-23 11:49	22222	Agent 2	2	Agent 3
29-12-23 12:49	33333	Agent 2	1
29-12-23 15:42	33333	Agent 1	2	Agent 2

I've found solution for this, it works, but it is very slow... Almost impossible to work when i have big volumes of data (currently i have 34 000 rows).

Here is the solution i've found: 

Could you, please, help me with alternative in DAX that is faster ?

Thank you very much !

[Ashish_Mathur]

Hi,

This should ideally be solved with a calculated column formula

Previous agent = CALCULATE(MAX(Data[Agent]),FILTER(Data,Data[Phone number]=EARLIER(Data[Phone number])&&Data[Date]<EARLIER(Data[Date])))

Hope this helps.

[Dimi_2207]

Thank you very much, it works great and quickly with the initial sample i've provided, where i have: Agent 1, Agent 2, Agent 3...

Actually, i have names in that column (sorry, i had to be more precise from the start!)

And, in this case, this does not work...Please, see, updated sample size.

Expectation: 

Phone	Date	Agent	Previous agent
111	08-11-23 08:24	Kate
111	08-11-23 09:48	Alex	Kate
111	08-11-23 10:38	Zara	Alex
111	08-11-23 10:57	Michael	Zara
111	08-11-23 14:36	Nataly	Michael
111	08-11-23 15:54	Yohan	Nataly
111	09-11-23 08:38	Sofia	Yohan
111	09-11-23 10:14	Sofia	Sofia
111	09-11-23 10:15	Rik	Sofia
222	13-12-23 16:02	Rik
222	14-12-23 16:03	Amanda	Rik
333	22-12-23 19:25	Maria
333	23-12-23 18:54	Cyril	Maria
333	23-12-23 19:01	Ivan	Cyril
222	27-12-23 13:49	Nikolas	Amanda
333	28-12-23 08:56	Elena	Ivan

What Power BI shows:

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

Column = LOOKUPVALUE(Data[Agent],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Phone]=EARLIER(Data[Phone])&&Data[Date]<EARLIER(Data[Date]))),Data[Phone],Data[Phone])

Hope this helps.

[Dimi_2207]

Thank very much !

Works perfectly

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

@Dimi_2207 Try a calculated column perhaps? Generally looks like MTBF without the final step, you just want the previous value, not a difference or any calculation. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous