Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ashaikh
Helper III
Helper III

Time calculation

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

1 ACCEPTED 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] )
    )

time_calculation

Best Regards,

Dale

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

View solution in original post

5 REPLIES 5
v-jiascu-msft
Microsoft Employee
Microsoft Employee

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

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

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

time_calculation

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi Abdulquadir,

 

Could you please post the steps how we can get 1:20PM? 

 

Best Regards,

Dale

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

@v-jiascu-msft thats the calculation I am looking for. I have already mentioned how that number comes.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.