Calculate average response time inside business hours only

[Aahubs]

Hi everyone,

My goal is to calculate Average Response time(HH:NN) for tickets submitted. Im looking at data for ticket creation time and ticket response time and calcuatling the response Duration difference. One issue we have is when a ticket is submitted outside business hours(8am- 5pm)or late in the day the response duration time adds up until the next business day when the ticket is responded to which adds to the response time and i dont want that.  I only want to count Response duration time during business hours. I currently have a filter applied to the saturday and sunday to remove these rare tickets from the calcualtion as they are not wanted for this calcualtion.  Would this need changing if a ticket would be submitted late Friday and not responded to until Monday??

I have Response duration converted to decimal and then a measure calcuatiing the average and formatiting that to HH:NN displayed on a card as requried 

As a note - the data provided is old, hense why some tickets are well over 1-2 days for resposne time. The current situation is that resposne times are generally within an hour, but since some tickets sit overnight the average time is inflated

Im working with ODATA type and i have a Company Fiscal Calendar Excel Table

Any help would be greatly appreciatied

Thank you 

[Aahubs]

Hi Everyone,

After lots of digging and searching I was able to find my solution for this.  Another user had linked this in a previous questions on this form with a topic similar to Datediff excluding weekends. * This is not my code but i did find it here, i just want to pass on what i had found to answer this question. 

The result is formatted in decimal hours, i just converted that to seconds. I could then calcualte an average of seconds per ticket from creation to response, then convert that average seconds result back to HH:NN 

VAR _WorkStart =

TIME( 7, 00, 0 )

VAR _WorkFinish =

TIME( 18, 00, 0 )

VAR _1DayWorkingTime =

DATEDIFF( _WorkStart, _WorkFinish, HOUR )

VAR _StartDate =

DATEVALUE(Changes[creationDate]) // Set the start Date column here

VAR _EndDate =

DATEVALUE(Changes[combine extensive/simple date] ) // Set the End Date column here

VAR _StartDateTime =

TIMEVALUE(Changes[creationDate - Time] ) // Set the start Time column here

VAR _EndDateTime =

TIMEVALUE(Changes[combine extensive/simple time] ) // Set the End Time column here

VAR _DaysBetweenStartFinish =

ADDCOLUMNS( 'Calendar', "DayN", WEEKDAY( [Date], 2 ) ) // Use the Date table here

VAR _WorkingDaysBetweenStartFinish =

COUNTX(FILTER(_DaysBetweenStartFinish,

[Date] > _StartDate && [Date] < _EndDate && [DayN] < 6),

[DayN]) // Sunday and Saturday are weekend days in this calculation

VAR _Day1WorkingHour =

IF(WEEKDAY( _StartDate, 2 ) < 6,

( MAX( _WorkFinish, _StartDateTime ) - MAX( _StartDateTime, _WorkStart ) ) * 24,

0)

VAR _LastDayWorkingHour =

IF(WEEKDAY( _EndDate, 2 ) < 6,

( MIN( _WorkFinish, _EndDateTime ) - MIN( _EndDateTime, _WorkStart ) ) * 24,

0)

VAR _Duration =

IF(_StartDate = _EndDate&&WEEKDAY(_StartDate,2)<6,

MAX(( MIN( _WorkFinish, _EndDateTime ) - MAX( _WorkStart, _StartDateTime ) ) * 24,

0),

_WorkingDaysBetweenStartFinish * _1DayWorkingTime + _Day1WorkingHour + _LastDayWorkingHour

)

RETURN

_Duration

[Aahubs]

You are correct, sorry still new to Power BI. But this did not fix my measure. Still displaying 00 : 30

[lbendlin]

Yes, you need to decide what granularity you want (hour or minute level, for example) and then you need to calculate three different intervals

- from ticket submission time to end of business on the day the ticket was submitted (*)

- from beginning of business day to time of ticket response on the day the response was recorded

- full business hours for all  days in between (minus weekend and holidays)

* here you can add your late submission logic

[Aahubs]

Thank you for the response,

I’ve managed to figure out a few things from using the forum and have come up with something I think is close to what im looking for.
Ive created separate calculated columns using DAX to take Ticket Creation time and look and see if it’s in business hours(if not round to end of hours, we changed this to 7am-6pm)

Filter Hours Creation date = IF(HOUR([creationDate])>=18,TIME(18,0,0),

IF(hour([creationDate])<=6,TIME(7,0,0),

[creationDate]))

and then for days of the week filter, combined with Filter hours time i used

Creation Date Filter = IF(WEEKDAY(Incidents[creationDate],2)=7 ||WEEKDAY(Incidents[creationDate],2)=6,BLANK(),YEAR(Incidents[creationDate])&"/"&MONTH(Incidents[creationDate])&"/"&DAY(Incidents[creationDate])&" "&FORMAT([Filter Hours Creation date ],"Short Time"))

I’ve then used creation date Filter and made a column that shows the difference between creation and response date( i did the same and created a DAX formula to show filtered response date)

I can then get "Filtered seconds difference Column" as a Number

Below is what I’m using to subtract after hours seconds from the response duration to get my total seconds during business hours elapsed time

Response time Subtract after hours Seconds = IF(Incidents[Filtered seconds difference] >= 158400, [Filtered seconds difference]- 158400,// 4 days subtraction of after hours, equals 11 hours per day measured in seconds

IF(Incidents[Filtered seconds difference] >= 118800, [Filtered seconds difference]- 118800,// 3 days subtraction of after hours, equals 11 hours per day measured in seconds

IF(Incidents[Filtered seconds difference] >= 79200, [Filtered seconds difference]- 79200,// 2 days subtraction of after hours, equals 11 hours per day measured in seconds

IF(Incidents[Filtered seconds difference] >= 39600, [Filtered seconds difference]- 39600,// 1 days subtraction of after hours, equals 11 hours per day measured in seconds

[Filtered seconds difference]))))

This seems to work and removes the elapsed closed hours time.

Now I need to find a way to convert my filtered seconds to DD:HH:NN, along with Averaging each ticket to get a total average of response time. 


I’ve put the new filtered seconds column in a table and have averaged that table to get a number of 2077 seconds (34min)

I’ve created a measure to average the filtered time and its giving me an incorrect time of 00:30

filtered time measure = format(CALCULATE(AVERAGE(Incidents[Negatvie response filter final])),"HH:NN")

I feel like im close to the result but cant work it out, any further help with this is greatly appreciated 

[lbendlin]

 "mm"  is for months.  For minutes use "nn". 

FORMAT function (DAX) - DAX | Microsoft Docs