cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sffc
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:

TimeStampIDState
30-Mar-21 23:47:4850Event 1 Stop
30-Mar-21 23:44:1450Event 1 Start
30-Mar-21 23:44:1354Event 2 Stop
30-Mar-21 23:34:2554Event 2 Start
30-Mar-21 23:34:0754Event 2 Stop
30-Mar-21 23:34:0254Event 2 Start
30-Mar-21 23:32:5354Event 2 Stop
30-Mar-21 23:31:4454Event 2 Start
30-Mar-21 23:30:1554Event 2 Stop
30-Mar-21 23:29:4654Event 2 Start
30-Mar-21 23:16:0350Event 1 Stop
30-Mar-21 23:15:1250Event 1 Start
30-Mar-21 23:03:4250Event 1 Stop
30-Mar-21 23:01:5150Event 1 Start
30-Mar-21 22:56:1650Event 1 Stop
30-Mar-21 22:55:1350Event 1 Start
30-Mar-21 22:51:4350Event 1 Stop
30-Mar-21 22:51:0550Event 1 Start
30-Mar-21 21:46:0067Event 3 Stop
30-Mar-21 21:44:3767Event 3 Start
TimeIdEvent '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
sffc
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..

View solution in original post

6 REPLIES 6
sffc
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..

View solution in original post

v-yangliu-msft
Community Support
Community Support

Hi  @sffc  ,

Here are the steps you can follow:

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

v-yangliu-msft_0-1619671639893.png

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:

v-yangliu-msft_1-1619671639895.png

 

 

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.

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

Capture2.PNG

amitchandak
Super User
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)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

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

 

 

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.