Forum Discussion
Subtract Time Entries
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.
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
12 Replies
- amitchandak
Super User
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())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.
- Carmichael
Advocate III
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?
- Carmichael
I am not sure why but I'm really struggling to post anything here.
hopefully this works....
