How to calculate time difference between many start/stop events

[sffc]

I am not finding a good way online to calculate the time difference in Power BI Desktop when there are a multitude of events that are distinct from one another.  For example, I have 27+ different event names that would look something like this exactly in cronological order:

TimeStamp	ID	State
30-Mar-21 23:47:48	50	Event 1 Stop
30-Mar-21 23:44:14	50	Event 1 Start
30-Mar-21 23:44:13	54	Event 2 Stop
30-Mar-21 23:34:25	54	Event 2 Start
30-Mar-21 23:34:07	54	Event 2 Stop
30-Mar-21 23:34:02	54	Event 2 Start
30-Mar-21 23:32:53	54	Event 2 Stop
30-Mar-21 23:31:44	54	Event 2 Start
30-Mar-21 23:30:15	54	Event 2 Stop
30-Mar-21 23:29:46	54	Event 2 Start
30-Mar-21 23:16:03	50	Event 1 Stop
30-Mar-21 23:15:12	50	Event 1 Start
30-Mar-21 23:03:42	50	Event 1 Stop
30-Mar-21 23:01:51	50	Event 1 Start
30-Mar-21 22:56:16	50	Event 1 Stop
30-Mar-21 22:55:13	50	Event 1 Start
30-Mar-21 22:51:43	50	Event 1 Stop
30-Mar-21 22:51:05	50	Event 1 Start
30-Mar-21 21:46:00	67	Event 3 Stop
30-Mar-21 21:44:37	67	Event 3 Start
Time	Id	Event 'n'

I need to calculate the time difference between start and stop events for each of the distinct 27+ event types.  Any advice on the best way to accomplish this would be greatly appreciated.

[sffc · Accepted solution]

I gave up on Power BI and decided to convert in Excel then refresh the dataset in Power BI.  This would be my suggestion to anyone looking for a similar solution....

=IF(C2=C3, A2-A3,0) then custom format the value into hh:mm:ss..

[v-yangliu-msft]

Hi  @sffc  ,

Here are the steps you can follow：

1. Enter the power query through Transform data, Add Column – Index Column – From1

2. Create calculated column.

Group = ROUND(DIVIDE([Index],2),0)

Datediff =
var _max=MAXX(FILTER(ALL('Table'),[Group]=EARLIER([Group])),[Index])
var _starttime=CALCULATE(MAX([TimeStamp]),FILTER(ALL('Table'),[Index]=_max))
var _endtime=CALCULATE(MAX([TimeStamp]),FILTER(ALL('Table'),[Index]=_max-1))
var _datediff=DATEDIFF(_starttime,_endtime,SECOND)
return
IF([Index]=_max,_datediff,BLANK())

3. Result：

Best Regards,

Liu Yang

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

[GRN_VPR8]

Hi @v-yangliu-msft, in step 2, did you mean to create two different calculated columns?

[sffc]

Thanks, Liu.  In the screen capture below, implementing that with my data only returns negative numbers for some reason.  Thoughts?

[amitchandak]

@sffc , try a new column like

new column =
var _last = if(search("Stop",[State],,0) >0 , maxx(filter(table, [ID] =earlier([ID]) && [TimeStamp] <earlier([TimeStamp]) && search("Start",[State],,0) >0),[TimeStamp]), [TimeStamp])
return
if(search("Stop",[State],,0) >0 , datediff(_1, [TimeStamp], minute), 0)

[sffc]

I wrote a script to add start and stop strings to each event to see if I could get this suggestion implemented.  It is returning long values that are not in date format, and it errors out when I attempt to change them to dateTime format.   The screen capture below shows an example. 

 The scree  

[sffc]

Thanks for the quick reply and solution, amitchandak.  My apologies - I should add a clarification note that my events do not have start or stop strings in their name;  I had included the "event start" and "event stop" generically to show that back-to-back events of the same name occur where the first instance is a start and the second in sequence is always a stop. 

For example, "my first event" will always have a second "my first event" before any other events can occur.  It's basically a line shutting down (start time) and then starting back up (stop time) where I need to calculate the total amount of time duration the line had down time.

(I would note, however, the solution you suggested above appears to work if I were to add "start" and "stop" top the event names;  there are over 400k records, so that's the only reason I can't add that into the event names and use the proposed solution..) 

Thanks again in advance for any advice/suggestions.