cancel
Showing results for
Did you mean:
Frequent Visitor

Looking to get calculation for a Mean Time to Resolve incidents

HI looking to get a column which will show in hours and minutes  where it includes business days only. My 2 columns of data are Created Time which is in this format 1/3/2018 2:01:00 PM and column Completed time 1/8/2018 2:25:00 PM the mttr is 24 min for this example but i cannot seem to get the new mttr column to show the individual mttr per row. Any help most welcome

6 REPLIES 6
Established Member

Re: Looking to get calculation for a Mean Time to Resolve incidents

Was 1/3 & 1/8 Typos, or should your result time include the total minutes in business hours (counting 8 hours  a day or 24?) for the 3 days before the ticket was closed?

Frequent Visitor

Re: Looking to get calculation for a Mean Time to Resolve incidents

HI Sorry yes well spotted so the data example is below. The MTTR formula i have excludes non bus hours and non working days

 Created Time Completed Time MTTR 02/01/2018 20:17 08/01/2018 19:24 36:00

=(NETWORKDAYS(U2,V2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(V2,V2),MEDIAN(MOD(V2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(U2,U2)*MOD(U2,1),"17:00","8:00")

Moderator

Re: Looking to get calculation for a Mean Time to Resolve incidents

@KevinGaff,

Please check the method in the following video.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Re: Looking to get calculation for a Mean Time to Resolve incidents

HI i am still struggling to get this to work. My MTTR data that i am importing has a column B1 called Created Time and a column J1 that is called Resolved Time. The data is from row 2. I am looking how i can get a MTTR column added to do a network days type calculation in hours and mins. My Excel file has a network days formula in a column called Working days to resolve

=(NETWORKDAYS(B2,J2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(J2,J2),MEDIAN(MOD(J2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),"17:00","8:00")

I managed to get the start and end parameters added. What else do i need to do?

Moderator

Re: Looking to get calculation for a Mean Time to Resolve incidents

@KevinGaff,

You also need to create the fWH function shown in the video using M scripts in this similar thread, create a holiday table in Power BI Desktop, then invoke the fWH function to calculate working hours in your original table, which is described in the above video.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Re: Looking to get calculation for a Mean Time to Resolve incidents

Okay Thanks i'll need to look at how to create this formula function