Looking to get calculation for a Mean Time to Resolve incidents

[KevinGaff]

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

[fhill]

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?

[KevinGaff]

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")

[v-yuezhe-msft]

@KevinGaff,

Please check the method in the following video.
https://www.youtube.com/watch?v=GLIoDbOiJgw

Regards,
Lydia

[KevinGaff]

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?

[v-yuezhe-msft]

@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

[KevinGaff]

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