Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
I have requirement where I need to calculate no of days to complete for Change request to meet the SLA.
I have below table where "Time to Complete in Hours and Minutes","Number of days to complete","Created On (Day of the Week)", "Completed on( Day of week)" this are calculated columns of my table.
where this columns are calculated like below
Time to Complete in Hours and Minutes=Released At Time Stamp-Created At Time Stamp
Number of days to complete=24*((Time to Complete in Hours and Minutes*60)/1440)
I have to calculate the no of days by considering non-busniness days.
For example the change request 1085, created day of week is friday and completed day of week is tuesday.
so its contains saturday and sunday, we need to avoid this and need to consider only business days to show No of days to complete. Right now the result is showing 4 days. but actual result should be 3 days.
How to do that is there any way? to skip the non-business days.
Change Request | Type | Created At Time Stamp | Released At Time Stamp | Time to Complete in Hours and Minutes | Number of days to complete | Created On (Day of the Week) | Completed on( Day of week) |
100 | xyz | 1/20/20 17:46 | 1/22/20 19:43 | 49:57:46 | 2.08 | Monday | Wednesday |
1004 | xyz | 1/30/20 17:46 | 2/4/20 15:29 | 117:42:39 | 4.90 | Thursday | Tuesday |
1007 | xyz | 1/30/20 18:16 | 2/6/20 13:05 | 162:48:50 | 6.78 | Thursday | Thursday |
1017 | xyz | 1/30/20 19:05 | 1/31/20 17:26 | 22:20:33 | 0.93 | Thursday | Friday |
102 | xyz | 1/20/20 17:50 | 1/28/20 12:48 | 186:57:23 | 7.79 | Monday | Tuesday |
1030 | xyz | 1/30/20 19:45 | 2/10/20 17:21 | 261:36:14 | 10.90 | Thursday | Monday |
1037 | xyz | 1/30/20 20:11 | 2/4/20 20:48 | 120:37:26 | 5.03 | Thursday | Tuesday |
105 | xyz | 1/20/20 17:58 | 1/22/20 17:08 | 47:10:13 | 1.97 | Monday | Wednesday |
1078 | xyz | 1/31/20 10:42 | 2/4/20 14:45 | 100:03:03 | 4.17 | Friday | Tuesday |
1082 | xyz | 1/31/20 13:29 | 2/4/20 14:45 | 97:16:26 | 4.05 | Friday | Tuesday |
1085 | xyz | 1/31/20 13:45 | 2/4/20 14:45 | 96:59:54 | 4.04 | Friday | Tuesday |
1087 | xyz | 1/31/20 13:56 | 2/4/20 14:44 | 96:48:53 | 4.03 | Friday | Tuesday |
1090 | xyz | 1/31/20 14:15 | 1/31/20 17:27 | 3:11:41 | 0.13 | Friday | Friday |
1130 | xyz | 1/31/20 21:31 | 2/4/20 15:29 | 89:57:26 | 3.75 | Friday | Tuesday |
1137 | xyz | 1/31/20 22:32 | 2/4/20 15:29 | 88:56:14 | 3.71 | Friday | Tuesday |
115 | xyz | 1/20/20 18:36 | 1/28/20 12:48 | 186:12:17 | 7.76 | Monday | Tuesday |
124 | xyz | 1/20/20 19:08 | 1/22/20 18:25 | 47:16:35 | 1.97 | Monday | Wednesday |
146 | xyz | 1/20/20 20:12 | 1/23/20 16:12 | 68:00:05 | 2.83 | Monday | Thursday |
173 | xyz | 1/21/20 13:49 | 1/23/20 17:26 | 51:36:37 | 2.15 | Tuesday | Thursday |
174 | xyz | 1/21/20 14:02 | 1/29/20 20:17 | 198:15:18 | 8.26 | Tuesday | Wednesday |
179 | xyz | 1/21/20 14:34 | 1/28/20 12:47 | 166:12:53 | 6.93 | Tuesday | Tuesday |
223 | xyz | 1/21/20 18:21 | 1/23/20 16:02 | 45:41:08 | 1.90 | Tuesday | Thursday |
263 | xyz | 1/21/20 20:29 | 1/23/20 18:00 | 45:31:32 | 1.90 | Tuesday | Thursday |
264 | xyz | 1/21/20 20:32 | 1/22/20 17:41 | 21:09:43 | 0.88 | Tuesday | Wednesday |
265 | xyz | 1/21/20 20:39 | 1/23/20 11:35 | 38:56:22 | 1.62 | Tuesday | Thursday |
266 | xyz | 1/21/20 20:42 | 1/29/20 14:46 | 186:03:54 | 7.75 | Tuesday | Wednesday |
278 | xyz | 1/21/20 21:57 | 1/22/20 19:18 | 21:20:20 | 0.89 | Tuesday | Wednesday |
284 | xyz | 1/21/20 22:29 | 1/27/20 16:55 | 138:25:34 | 5.77 | Tuesday | Monday |
292 | xyz | 1/22/20 13:47 | 2/10/20 21:02 | 463:14:06 | 19.30 | Wednesday | Monday |
312 | xyz | 1/22/20 15:34 | 1/23/20 16:13 | 24:38:51 | 1.03 | Wednesday | Thursday |
315 | xyz | 1/22/20 16:12 | 1/27/20 20:15 | 124:02:39 | 5.17 | Wednesday | Monday |
337 | xyz | 1/22/20 17:42 | 1/30/20 9:43 | 184:00:55 | 7.67 | Wednesday | Thursday |
359 | xyz | 1/22/20 19:13 | 1/28/20 22:17 | 147:03:48 | 6.13 | Wednesday | Tuesday |
392 | xyz | 1/22/20 21:10 | 1/27/20 13:13 | 112:02:39 | 4.67 | Wednesday | Monday |
402 | xyz | 1/22/20 21:47 | 1/23/20 16:22 | 18:34:55 | 0.77 | Wednesday | Thursday |
403 | xyz | 1/22/20 21:47 | 1/28/20 19:07 | 141:19:52 | 5.89 | Wednesday | Tuesday |
430 | xyz | 1/23/20 11:41 | 1/23/20 16:06 | 4:24:35 | 0.18 | Thursday | Thursday |
432 | xyz | 1/23/20 12:00 | 1/23/20 16:05 | 4:04:21 | 0.17 | Thursday | Thursday |
442 | xyz | 1/23/20 14:02 | 1/24/20 13:21 | 23:18:52 | 0.97 | Thursday | Friday |
453 | xyz | 1/23/20 16:12 | 1/27/20 22:12 | 101:59:37 | 4.25 | Thursday | Monday |
469 | xyz | 1/23/20 19:01 | 1/23/20 19:59 | 0:57:58 | 0.04 | Thursday | Thursday |
471 | xyz | 1/23/20 19:18 | 1/23/20 20:01 | 0:43:10 | 0.03 | Thursday | Thursday |
519 | xyz | 1/23/20 22:00 | 1/28/20 13:06 | 111:05:10 | 4.63 | Thursday | Tuesday |
530 | xyz | 1/24/20 2:27 | 1/24/20 19:04 | 16:36:57 | 0.69 | Thursday | Friday |
538 | xyz | 1/24/20 3:31 | 1/24/20 15:49 | 12:17:51 | 0.51 | Thursday | Friday |
631 | xyz | 1/24/20 19:52 | 1/30/20 19:52 | 143:59:42 | 6.00 | Friday | Thursday |
646 | xyz | 1/24/20 21:33 | 1/27/20 13:37 | 64:03:59 | 2.67 | Friday | Monday |
694 | xyz | 1/27/20 17:37 | 1/28/20 13:12 | 19:35:14 | 0.82 | Monday | Tuesday |
73 | xyz | 1/20/20 16:02 | 1/23/20 16:04 | 72:01:51 | 3.00 | Monday | Thursday |
742 | xyz | 1/27/20 20:19 | 1/30/20 9:40 | 61:21:46 | 2.56 | Monday | Thursday |
746 | xyz | 1/27/20 20:53 | 2/7/20 16:38 | 259:44:32 | 10.82 | Monday | Friday |
788 | xyz | 1/28/20 17:01 | 1/31/20 21:11 | 76:09:27 | 3.17 | Tuesday | Friday |
887 | xyz | 1/29/20 16:17 | 2/7/20 16:38 | 216:20:44 | 9.01 | Wednesday | Friday |
978 | xyz | 1/30/20 13:18 | 1/30/20 19:55 | 6:36:32 | 0.28 | Thursday | Thursday |
995 | xyz | 1/30/20 16:27 | 1/30/20 19:34 | 3:07:44 | 0.13 | Thursday | Thursday |
Solved! Go to Solution.
@Krutigawale33 , for business days refer my video
https://www.youtube.com/watch?v=Qv4wT8_P-AA
or this
https://curbal.com/blog/glossary/networkdays-dax
Business hours
https://exceleratorbi.com.au/calculating-business-hours-using-dax/
How to deal with duration
Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
@Krutigawale33 , for business days refer my video
https://www.youtube.com/watch?v=Qv4wT8_P-AA
or this
https://curbal.com/blog/glossary/networkdays-dax
Business hours
https://exceleratorbi.com.au/calculating-business-hours-using-dax/
How to deal with duration
Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
Hello @amitchandak ,
I am able to achieve that. But does it considers the timestamp?
Means Created Time will have time 9/21/2021 1:57:33 PM and Released Time 9/22/2021 11:53:03 AM with this if gets difference then will get 9:55:30 , completed in 1 day but with above approach it considers 2 workdays
Any idea how to do that
Hello Amit,
I am getting this error while creating DateDiff column. How to deal with this
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |