Get data from next/previous row

[Rallmo]

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!

[amitchandak · Accepted solution]

@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=17820s

Power Query- Index Column: https://youtu.be/NS4esnCDqVw

[Rallmo]

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.