Subtract 2 dates to a response time in hours

[matdune56]

So I'm using PowerBI to interrogate an OSticket system

In  table Ticket, I've got the date when the ticket was created. "OSticket_OST_ticket['Created]'

In a thread table (which captures all the updates for each ticket) "OSticket_OST_Thread_Entry", I've filtered on field "Type" to be "R" (response), and in a visual (below)  displaying the first date entry in this table i.e. the first true response to the ticket ("Earliest created") - which uses the "created" field in the thread entry table.

so as you can see for the first ticket (ending 649), I've got the ticket creation date and earliest response. 2 minute response in this case

What I want to do is get the repsonse time in hours, i.e. the difference between when the ticket was created and the first Response.

appreciate your help. thanks

[jtownsend21]

I believe this should work for you. 

Response Time in Hours = 
VAR TIME_DELTA_HOURS = 
    [Created] - [Earliest Created]
RETURN
    FORMAT(
        TIME_DELTA_HOURS,
        "HH"
    )

[matdune56]

I think I'm nearly there with getting the "earliest created" field from the thread

Responsefirst = MINX(FILTER(RELATEDTABLE('osticket ost_thread_entry'),[type]="R"),[created])

[matdune56]

I think your hour calcualtion only works if they are on the same day

if somethings not been responded to for days, it just uses the hour difference between

so something that started on the 10th April 08:00, responded to on 15th April 15:00, the system calculates that as 7, which it's more than 100.

I used this

ResponseTimeHours = DATEDIFF([Created],[Responsefirst],HOUR)

[jtownsend21]

Interesting. I didn't know that it was only going to look at the hours portion. 

Did the DateDiff work for you? 

[matdune56]

yes thanks, I've got the date into a table view - which is good as it filters out any duplicates.

However is there anyway to take whats in the table view and put it into a line graph - I need duplicate records removed otherwise it will make the average/max response times look huge. Do I have to do some kind of prequery and drop that into the line graph ? Sorry  , new to this.

[jtownsend21]

It sounds like you should just be able to drop it into a line graph, is it doing something strange when you try?

I assume you mean with Response time on the Y axis, but what is on the X axis? 

[matdune56]

Hi, thanks for that

however, is it possible to read direct from the visual - as that's doing the work of using fields from different tables and also taking the first record linked with a filter.

the "Earliest created" visual is created by taking the first record of the thread entry table which is also filtered.

i.e. the SQL to reproduce and extract the values that the visual is showing is what I'm after. ie. combining 2 tables, but actually picking up one specific record from the 2nd table to get "earliest created".

thanks again