cancel
Showing results for
Did you mean:
Regular Visitor

## How to calculate time difference between many start/stop events

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.

1 ACCEPTED SOLUTION
Regular Visitor

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..

6 REPLIES 6
Regular Visitor

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..

Community Support

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.

Regular Visitor

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

Super User

@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)

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Regular Visitor

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

Regular Visitor

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..)

Announcements

#### Launching new user group features

Learn how to create your own user groups today!