Forum Discussion
Get data from next/previous row
Hi
I have a problem that I cant figure out how to solve, wasn't sure on where this is best solved (DAX or M-code) so therefore I put it under Desktop. 🤷
In our ticketing system I want to calculate the actual time a ticket is open. I have the requestDate when the ticket is created and the completionDate when we consider the ticket completed. Between those two dates its easy to just do a Datediff and be done with it. But I want to exclude the period when we are waiting for information from the end user. I have the start time for that under onHoldDate but I dont have the date and time for when the ticket is active again and we should stop considering the ticket as On Hold.
So for every time the ticket is On Hold I would like to collect the timeStamp information from the following row and store it in a separate column preferably.
Do I need a index column to reference? Would you solve this is Power Query or with DAX? Is there a best practice I should use?
Here's a sample of what my data looks like.
| timeStamp | ticketId | requestDate | completionDate | onHoldDate | statusId | Status |
| 2023-07-20 14:24 | e1729777 | 2023-07-20 14:24 | 225ded26 | Logged | ||
| 2023-07-25 12:35 | e1729777 | 2023-07-20 14:24 | 2023-07-21 07:43 | 4fbc9b46 | Waiting for user | |
| 2023-07-26 08:00 | e1729777 | 2023-07-20 14:24 | 1ee72955 | In progress | ||
| 2023-07-26 08:00 | e1729777 | 2023-07-20 14:24 | 1ee72955 | In progress | ||
| 2023-07-26 10:25 | e1729777 | 2023-07-20 14:24 | 1ee72955 | In progress | ||
| 2023-07-26 10:26 | e1729777 | 2023-07-20 14:24 | 1ee72955 | In progress | ||
| 2023-07-26 11:33 | e1729777 | 2023-07-20 14:24 | 2023-07-26 11:33 | 4fbc9b46-82ef | Waiting for user | |
| 2023-07-27 14:07 | e1729777 | 2023-07-20 14:24 | 1ee72955 | In progress | ||
| 2023-07-27 14:08 | e1729777 | 2023-07-20 14:24 | 1ee72955 | In progress | ||
| 2023-07-27 14:30 | e1729777 | 2023-07-20 14:24 | 1ee72955 | In progress | ||
| 2023-07-28 07:36 | e1729777 | 2023-07-20 14:24 | 2023-07-28 06:55 | 4fbc9b46 | Waiting for user | |
| 2023-07-28 07:37 | e1729777 | 2023-07-20 14:24 | 2023-07-28 07:37 | ca371597 | Completed | |
| 2023-07-28 07:37 | e1729777 | 2023-07-20 14:24 | 2023-07-28 07:37 | ca371597 | Completed | |
| 2023-08-02 07:37 | e1729777 | 2023-07-20 14:24 | 2023-07-28 07:37 | 4350a7dd | Closed |
Thanks in advance!
Rallmo , Index can help.
One of the way to get diff , say for timestamp
New Column =
var _max = maxx(filter(Table, [TicketID] = earlier([TicketID]) && [timestamp] <earlier( [timestamp]) ), [timestamp])
return
datediff(_max , [timestamp], second)
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820sPower Query- Index Column: https://youtu.be/NS4esnCDqVw
2 Replies
Rallmo , Index can help.
One of the way to get diff , say for timestamp
New Column =
var _max = maxx(filter(Table, [TicketID] = earlier([TicketID]) && [timestamp] <earlier( [timestamp]) ), [timestamp])
return
datediff(_max , [timestamp], second)
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820sPower Query- Index Column: https://youtu.be/NS4esnCDqVw
Thanks for your response amitchandak. My post got marked as spam so I forgot to follow up here. I solved it with an index, but I'll for sure try your solution aswell.
