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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Employee
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
Employee
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.