Forum Discussion
Solved
To show previous value based another column in DAX
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 ...
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.
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:
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.
Thank very much !
Works perfectly
You are welcome.