How do I calculate average Response Time only between work hours 8AM-1AM

[mpalha04]

Hello,

I have been asked to find the average response time for orders in working hours (8AM-1AM) between 2 dates for a helpdesk system. I have 2 columns in seperate tables: Order Time and Message Sent Time. 

The response time should only be calculating during working hours. That being said if the Order Time is sent not during working hours, the response time will calculate beginning 8AM. 

The response time should not include weekends as well.

Any help with this is greatly appreciated. There is no NETWORKINGDAYS function in DAX, so I'm stumbling.

[v-danhe-msft · Accepted solution]

Hi @mpalha04,

Based on my test, you can refer to below steps:

1.I have entered some sample date:

2.Create three calculated columns and a measure.

Calculated columns:

filter hours = IF(HOUR('Table1'[OrderDate])<=8,TIME(8,0,0),[OrderDate])

Filter Order Date = IF(WEEKDAY('Table1'[OrderDate],2)=7 ||WEEKDAY('Table1'[OrderDate],2)=6,BLANK(),YEAR('Table1'[OrderDate])&"/"&MONTH('Table1'[OrderDate])&"/"&DAY('Table1'[OrderDate])&" "&FORMAT([filter hours],"Short Time"))

Hours = DATEDIFF('Table1'[Filter Order Date],'Table1'[MessageDate],MINUTE)/60

Measure:

Ave Hours = AVERAGE(Table1[Hours])

3.Create a Card visual and you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/y8r55uny5g32uwn/How%20do%20I%20calculate%20average%20Response%20Time%20onl...

Regards,

Daniel He

[mpalha04]

Hi Daniel,

This is great!

The only issue is when i create a table visualization for OrderDate, MessageSentTime, and Hours, the hours for weekend dates are blank. If order date is on a weekend, it is not considered working hours, so hours would need to start calculating from the following Monday at 8AM. 

You can refer to the attached screenshot. OrderDate is on July 21, which is a weekend. For MessageSentTimes that are past July 23 8AM, there should be a number for hours. 

Also, if its possible, Hours that are blank should be "0.00" (both OrderedDate and MessageTime not on a working hour)?

[mpalha04]

Also, there seems to be some calculation issues if a OrderDate is within working hours and MessageSentTime is not within working hours.

I highlighted some examples. From top to bottom, Hours should be approx. 5, 2, 17.5.

In essence, working hours should not be calculating between 1AM and 8AM Monday-Friday. 

[mpalha04]

I fixed the above by repeating the 3 calculated columns in your solution. I then fixed the Hours measure to find the difference between the 2 Filter Order Dates.

Still having trouble with weekend dates

[mpalha04]

Just an update on this... my formula works for dates when OrderDate and MessageSentTime are during the same day. It does not work if MessageSentTime is on the next day.

In the highlighted case, the SLA should be about 16 hours.