Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
Proud to give back to the community!
Thank You!
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")
@KevinGaff,
Please check the method in the following video.
https://www.youtube.com/watch?v=GLIoDbOiJgw
Regards,
Lydia
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?
@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
Okay Thanks i'll need to look at how to create this formula function
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |