Subtract Time Entries

[Anonymous]

Hello and thank you for taking the time to review my request.

I am trying figure out a way to subtract two time entries based on the following condition;

If 'Event Id' has the same value AND contains a value in the 'On Scene Separation' and 'Notified Separation', subtract the 'Notified Separation' value from the 'On Scene Separation' value.

For example, 'Event ID' entries 468739 and 468788 both have values in 'On Scene Separation' and 'Notified Separation' columns. For these entries, I would like to be able to subtract '4:12:00' from '4:28:00' (Event ID 468736) as well as subtract '10:07:00' from '10:20:00' (Event ID 468788). Ultimately I'd like to average those differences for a selected range using a slicer in the report.

Let me know if you need more explanation...sometimes it's clearer in my mind.

[Carmichael]

Any chance you can could maybe use power query to reshape your data?  It sounds like for this specific report you might be better off having the times in different columns?

[Anonymous]

I appreciate you taking a look at this. I'm new to coding in Power BI but I feel like this is something can be accomplished.

I can narrow it down to the following...any suggestions for the next step?

[Carmichael]

I am not sure why but I'm really struggling to post anything here. 

hopefully this works....

[Carmichael]

Is it working now?

I have screen shots to show you but let me see if I can just post the text first. 

1. merge the event and responder columns to create a unique ID

2. pivot the action putting it into columns. Ensure that under advanced the response time is not aggregated. 

that will get your times into columns by action. 

3. Select the columns you want to subtract. On ribbon under add column, click on time (the clock) and subtract. That should give you the difference value. 

note if one of the times isn't there then it doesn't return a result. Which is what I think you want. 

4. close and load into power bi. 
5. Build dax measure for average... you may even be able to get away with just putting the difference value into the visual and then changing to an average in the fields well. Not best practice but if gives you what you need then tick. 

[Carmichael]

Let me know if you need the screenshots. 

[Anonymous]

Yeah, could you provide screen shots. I'm sorry, I feel bad. I'm having trouble finding how to merge the Event Id and Responder Id column.

[Carmichael]

Ok will try again now. But here is how to merge columns. 
https://support.microsoft.com/en-us/office/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9...

[Carmichael]

Note - I used an online image scrape of one the images you posted to get the data. Not all came through accurate so I removed those columns. 

1. the merged column (please see article on how to accomplish above)

2. pivot column (https://support.microsoft.com/en-us/office/pivot-columns-abc9c8da-3be9-44c4-886e-0be331ab387a)

3. subtract to get time difference

4. assuming you know how to load

5. that might be another post to create the measure. 

hope that helps 

[Anonymous]

That worked!!! That's awesome...I can't thank you enough.

One more question if you don't mind...how can I calculate the average of the response time? I tried to create a quick measure but my values look odd.

[Carmichael · Accepted solution]

A measure would be best practice (https://docs.microsoft.com/en-us/dax/average-function-dax)

However, you may look to just use the standard aggregation facility.

https://docs.microsoft.com/en-us/power-bi/create-reports/service-aggregates

Create a bar chart.

x = Responder Type

y = response time

click on the values well and change the value from sum to average.

If I have answered your question please mark  my post as the solutiuon.  If any of my posts helped please 'like' them.  Appreciate the Kudos.

Thanks

[amitchandak]

@Anonymous , Please try as a new column

new column =
var _min = minx(filter(Table, [Event Id] = earlier([Event Id])),[Notified Separation])
var _max = maxx(filter(Table, [Event Id] = earlier([Event Id])),[On Scene Separation])
return
_max - _min

new column =
var _min = minx(filter(Table, [Event Id] = earlier([Event Id])),[Notified Separation])
var _max = maxx(filter(Table, [Event Id] = earlier([Event Id])),[On Scene Separation])
return
if( not(isblank([Notified Separation])), _max - _min , blank())

[Anonymous]

I'm at a loss for where to begin with this and I don't know if what I'm asking is even possible. Here is what I'm trying to accomplish;

My final result is going to be what I'm going to call Avg Response Time. This will be the difference of the OnScene time minus the Notified time for a common Event Id, Responder Id, and Responder Type. I then want to be able to average the result based on certain date ranges which I imagine can be controlled by a slicer.

Here is snippet of my raw data. Using Event Id 468739 as an example, the Responder Id 5943, Responder Type Motorist Assist, yields an OnScene value of 4/1/2020 4:28 and an Notified value of 4/1/2020 4:12. The calculation will be OnScene - Notified (4/1/2020 4:28 - 4/1/2020 4:12).

Here for 468788 the return will be Responder Id 5627, Responder Type Motorists Assist, OnScene - Notified (4/1/2020 10:20 - 4/1/2020 10:07)

Thanks again for reviewing my request and let me know if you need any additional information.