Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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..
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..
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.
Hi @v-yangliu-msft, in step 2, did you mean to create two different calculated columns?
Thanks, Liu. In the screen capture below, implementing that with my data only returns negative numbers for some reason. Thoughts?
@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)
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |