Calculating the value of time difference between rows

[niel_orvyn1]

Hi there,

New to power Bi and trying to move away from excel

I'm trying to calculate the time difference between two rows. 

Below table "Time Diff" is the difference between the the previous row and the next row in  "Time" column. 

(Context: Trying to determine the total time in minutes,seconds between events in order to apply a threshold filter) 

Event	Cam	Event time stamp	Date	Date	Month	Time	Time diff	Over Threshold
lens_dirty	cam_465	1564633771	01/08/2019 04:29	01/08/2019	August	05:34:31	05:34:31	TRUE
lens_dirty	cam_465	1564637671	01/08/2019 05:34	01/08/2019	August	06:34:31	01:00:00	TRUE

Let me know if you are able to assist and if my question is clear, else I can upload the excel sheet. 

Thanx

[Anonymous · Accepted solution]

you need to create index column and then you have to use lookupfunction as below

Column=lookupvalue(table[time],table[index],table[index]+1,table[event],table[event])

This will return time of next row for current row for same event.

Then simple use datediff as below

difference column=datediff(table[time],Column,Sec)

incase you want measure

difference measure=datediff(max(table[time]),max(Column),Sec)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous · Accepted solution]

you need to create index column and then you have to use lookupfunction as below

Column=lookupvalue(table[time],table[index],table[index]+1,table[event],table[event])

This will return time of next row for current row for same event.

Then simple use datediff as below

difference column=datediff(table[time],Column,Sec)

incase you want measure

difference measure=datediff(max(table[time]),max(Column),Sec)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[niel_orvyn1]

Much appreciated! Worked perfectly. 

[niel_orvyn1]

I've run into the following problem:

The index+1 on the 1st timestamp of the month does not return a value. (Highlighted in yellow)

So when I subract the "PrevLookup" from the "timestamp" I get a value but would like it to be zero in order to get the sum of the "Diff" colum. However, the 63161281 gives me an incorrect figure. 

I can remove it with a filter but applying more measure down the line I run into problems. 

Is there something I can add to the line or is there a workaround? 

Best

[Anonymous · Accepted solution]

update formula as below

prevRlookup=

var init=same formula last one

return

if(init=blank(),"expected value",init)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[niel_orvyn1]

it works but with "expected value" being text, cannot convert it to date or number. So timedifference between to two date columns wont work. 

prevRlookup=

var init=same formula last one

return

if(init=blank(),"expected value",init)

Appreciate your help and sorry to keep asking.