Calculating Next Row

[BenBrubacher]

I need help to calculate how long it takes an agent to handle each page during their call, and I need to calculate the value from the next row.

I have the following transactional table which records every page visit. Each row only has the page start time, it does not have the page end time (note it does have the call end time but that is different from the page end time). 

The page end time would be the pagestart time from the next row/page they visit next, except the last record for each call will not have a next page so it would be page start time minus call end time

The number of pages varies, sometimes 20-30 pages, sometimes only 2 pages, and you could have the agent jump around such as going back to previous pages. 

I ultimately want my table to look like this. 

I wrote the following calculated column, but wondering if there is a different way to write it as I run out of memory and I think this field is the problem (a measure maybe)?

PageEndTime =

var nextRow=

TOPN(

1,

FILTER(vwCallScriptFlow, vwCallScriptFlow[PageStartTime] > EARLIER (vwCallScriptFlow[PageStartTime])

&& vwCallScriptFlow[OutboundHistoryID] = EARLIER(vwCallScriptFlow[OutboundHistoryID])

),

vwCallScriptFlow[PageStartTime],ASC

)

var nextvalue= MinX( nextrow, vwCallScriptFlow[PageStartTime])

var fin = if(ISBLANK(nextvalue),vwCallScriptFlow[CallEndTime],nextvalue)   \\ if it is the last record I set the page end time to be the call end time

return finalnextrow

This does return results, but like i said, I dont think it is the right way to tackle this problem?

[BenBrubacher]

Images didnt post properly, heres the current dataset

Here's what I want it to look like?

[V-lianl-msft · Accepted solution]

Hi @BenBrubacher ,

Based on your description, I think creating an index column based on page number in the query editor will make things easier.

Then use this index column and the ALL function to get the result.

PET = VAR _INDEX = SELECTEDVALUE('Table'[Index])
VAR _PET = CALCULATE(MAX('Table'[PST]),FILTER(ALL('Table'),'Table'[Index]=_INDEX+1))
RETURN IF(ISBLANK(_PET),MAX('Table'[CET]),_PET)

D = VAR _PET = [PET] 
VAR _PST = MAX('Table'[PST])
RETURN (_PET-_PST)*86400

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.