Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I am looking to do some time calculation on a DateTime and would want to know if there is an easier way to do it. What I am trying to do is basically calculate SLA DueTime which is same business day i.e 8hrs, Business day is 9am - 5pm which is 8hrs
But in our case how the same business day work is; if a ticket comes in anytime during the day then 8 hrs will be calculated as remaining business day during same day out of 8hrs and whats balance of 8 hrs will be on next day.
Below is the example of that.
DateTime column data - 6/27/2018 1:40 PM
Now for today till 5pm we have 3hrs 20mins, so out of 8hrs we are left with 4hr 40mins, So in this case SLA due date will be 6/28/2018 1:20 PM.
I would like understand how will I be able to do this calculation.
Any help will be appreciated.
Thanks
Abdulquadir
Solved! Go to Solution.
Hi @ashaikh,
Try this formula as a calculated column please.
Column = VAR currentDay = DATEVALUE ( [Date] ) VAR nextWorkDay = CALCULATE ( MIN ( 'Calendar'[Date] ), FILTER ( 'Calendar', 'Calendar'[Date] > currentDay && 'Calendar'[isWorkDay] = 1 ) ) RETURN IF ( TIMEVALUE ( [Date] ) + TIME ( 8, 0, 0 ) <= TIME ( 17, 0, 0 ), [Date] + TIME ( 8, 0, 0 ), nextWorkDay + TIMEVALUE ( [Date] ) )
Best Regards,
Dale
Hi Abdulquadir,
I'm afraid I have no idea about how to get "1: 20PM".
1. 6/28/2018 9:00 AM + 3hrs 20 mins is 6/28/2018 0:20 PM;
2. 6/28/2018 9:00 AM + 4hrs 40 mins is 6/28/2018 1:40 PM;
So how can we get 6/28/2018 1:20 PM?
Best Regards,
Dale
Yes you are right. It should be 1:40PM. So it will be 6/28/2018 1:40 PM
But the main point here which I wanted to show is SLA is 8 hrs and with in a business day so if 8 hrs is completed with in todays business date then Due Date will be next day with remaining time from start of business day i.e 8:00 am
Hi @ashaikh,
Try this formula as a calculated column please.
Column = VAR currentDay = DATEVALUE ( [Date] ) VAR nextWorkDay = CALCULATE ( MIN ( 'Calendar'[Date] ), FILTER ( 'Calendar', 'Calendar'[Date] > currentDay && 'Calendar'[isWorkDay] = 1 ) ) RETURN IF ( TIMEVALUE ( [Date] ) + TIME ( 8, 0, 0 ) <= TIME ( 17, 0, 0 ), [Date] + TIME ( 8, 0, 0 ), nextWorkDay + TIMEVALUE ( [Date] ) )
Best Regards,
Dale
Hi Abdulquadir,
Could you please post the steps how we can get 1:20PM?
Best Regards,
Dale
@v-jiascu-msft thats the calculation I am looking for. I have already mentioned how that number comes.
User | Count |
---|---|
84 | |
69 | |
69 | |
65 | |
54 |
User | Count |
---|---|
93 | |
93 | |
91 | |
76 | |
69 |