Forum Discussion
How do I calculate average Response Time only between work hours 8AM-1AM
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.
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.
Regards,
Daniel He
5 Replies
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.
Regards,
Daniel He
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)?
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.
