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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Calculate business hours and days

Hi!

 

I have some data with time/date divided into steps of a process. I need to calculate business hours (8 to 18, 24h format) and days between each step to know how much time each step takes to be completed.

 

Exemple (dd/mm/yyyy hh:mm:ss):

IdStep 1 (Start)Step 2Step 3Step 4 (End)
110/08/2021 10:47:3210/08/2021 16:03:3014/08/2021 12:12:0815/08/2021 17:54:02
221/09/2021 12:30:4122/09/2021 11:02:5622/09/2021 12:56:0130/09/2021 11:05:34
301/10/2021 18:01:0103/10/2021 17:03:3104/10/2021 08:57:0104/10/2021 10:14:31

 

I've tried to find posts of people with the same problem of myself, and even though i've found some interesting answers like this, this, and this one, the one that got closed to solving my problem (without calculating the business days still) is the one showed in this website.

 

 

 

The problem is that i need to replicate this not just one time, but for every step of the process (unless there is another way that i'm unaware of). So i need help finding a way to calculate this without having my power bi loaded with columns taking a lot of time to process. I hope you guys can assist me, thanks in advance!

 

Best regards.

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

You can create measure and use variable to store each step time and quote them when necessary. To calculate the timediff, the measure could be like this:

TimeDiff = 
VAR s1 =
    SELECTEDVALUE ( 'Table'[Step 1 (Start)] )
VAR s2 =
    SELECTEDVALUE ( 'Table'[Step 2] )
VAR s3 =
    SELECTEDVALUE ( 'Table'[Step 3] )
VAR s4 =
    SELECTEDVALUE ( 'Table'[Step 4 (End)] )


VAR s2_1_total_second =
    DATEDIFF ( s1, s2, SECOND )
VAR s2_1_total_day =
    ROUNDDOWN ( s2_1_total_second / 3600 / 24, 0 )
VAR s2_1_hour =
    ROUNDDOWN ( ( s2_1_total_second - s2_1_total_day * 24 * 3600 ) / 3600, 0 )
VAR s2_1_minute =
    ROUNDDOWN (
        ( s2_1_total_second - s2_1_total_day * 24 * 3600 - s2_1_hour * 3600 ) / 60,
        0
    )
VAR s2_1_second =
    ROUNDDOWN (
        ( s2_1_total_second - s2_1_total_day * 24 * 3600 - s2_1_hour * 3600 - s2_1_minute * 60 ),
        0
    )
VAR s2_1_time = s2_1_total_day & " day(s) " & s2_1_hour & ":" & s2_1_minute & ":" & s2_1_second


VAR s3_2_total_second =
    DATEDIFF ( s2, s3, SECOND )
VAR s3_2_total_day =
    ROUNDDOWN ( s3_2_total_second / 3600 / 24, 0 )
VAR s3_2_hour =
    ROUNDDOWN ( ( s3_2_total_second - s3_2_total_day * 24 * 3600 ) / 3600, 0 )
VAR s3_2_minute =
    ROUNDDOWN (
        ( s3_2_total_second - s3_2_total_day * 24 * 3600 - s3_2_hour * 3600 ) / 60,
        0
    )
VAR s3_2_second =
    ROUNDDOWN (
        ( s3_2_total_second - s3_2_total_day * 24 * 3600 - s3_2_hour * 3600 - s3_2_minute * 60 ),
        0
    )
VAR s3_2_time = s3_2_total_day & " day(s) " & s3_2_hour & ":" & s3_2_minute & ":" & s3_2_second


VAR s4_3_total_second =
    DATEDIFF ( s3, s4, SECOND )
VAR s4_3_total_day =
    ROUNDDOWN ( s4_3_total_second / 3600 / 24, 0 )
VAR s4_3_hour =
    ROUNDDOWN ( ( s4_3_total_second - s4_3_total_day * 24 * 3600 ) / 3600, 0 )
VAR s4_3_minute =
    ROUNDDOWN (
        ( s4_3_total_second - s4_3_total_day * 24 * 3600 - s4_3_hour * 3600 ) / 60,
        0
    )
VAR s4_3_second =
    ROUNDDOWN (
        ( s4_3_total_second - s4_3_total_day * 24 * 3600 - s4_3_hour * 3600 - s4_3_minute * 60 ),
        0
    )
VAR s4_3_time = s4_3_total_day & " day(s) " & s4_3_hour & ":" & s4_3_minute & ":" & s4_3_second


RETURN
    s4_3_time

vyingjl_0-1633572404502.png

 

Best Regards,
Community Support Team _ Yingjie Li
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

3 REPLIES 3
mahoneypat
Microsoft Employee
Microsoft Employee

You should unpivot your data and split your DateTime columns into Date and Time columns, and do this as a measure.  However, with your data structued as shown, you can add a calculated column with this expression as an example of how to approach this.  It calculates the total time (in days as a decimal) between Step 1 and 4 and then subtracts out 16 hrs for the number of days it spans.  Note that your Step 4 in the first row finishes after 16:00 hrs, so not sure if you would count the full time on that day or stop at 4 PM.  Replace BusTime with your actual table name.

 

Total Business Days =
VAR totaltime =
    FIXED ( BusTime[Step 4 (End)] - BusTime[Step 1 (Start)], 6 )
VAR nights =
    INT ( BusTime[Step 4 (End)] - BusTime[Step 1 (Start)] )
VAR result = totaltime - nights * 16 / 24
RETURN
    ROUND ( result5 )
 
 
Pat
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


v-yingjl
Community Support
Community Support

Hi @Anonymous ,

You can create measure and use variable to store each step time and quote them when necessary. To calculate the timediff, the measure could be like this:

TimeDiff = 
VAR s1 =
    SELECTEDVALUE ( 'Table'[Step 1 (Start)] )
VAR s2 =
    SELECTEDVALUE ( 'Table'[Step 2] )
VAR s3 =
    SELECTEDVALUE ( 'Table'[Step 3] )
VAR s4 =
    SELECTEDVALUE ( 'Table'[Step 4 (End)] )


VAR s2_1_total_second =
    DATEDIFF ( s1, s2, SECOND )
VAR s2_1_total_day =
    ROUNDDOWN ( s2_1_total_second / 3600 / 24, 0 )
VAR s2_1_hour =
    ROUNDDOWN ( ( s2_1_total_second - s2_1_total_day * 24 * 3600 ) / 3600, 0 )
VAR s2_1_minute =
    ROUNDDOWN (
        ( s2_1_total_second - s2_1_total_day * 24 * 3600 - s2_1_hour * 3600 ) / 60,
        0
    )
VAR s2_1_second =
    ROUNDDOWN (
        ( s2_1_total_second - s2_1_total_day * 24 * 3600 - s2_1_hour * 3600 - s2_1_minute * 60 ),
        0
    )
VAR s2_1_time = s2_1_total_day & " day(s) " & s2_1_hour & ":" & s2_1_minute & ":" & s2_1_second


VAR s3_2_total_second =
    DATEDIFF ( s2, s3, SECOND )
VAR s3_2_total_day =
    ROUNDDOWN ( s3_2_total_second / 3600 / 24, 0 )
VAR s3_2_hour =
    ROUNDDOWN ( ( s3_2_total_second - s3_2_total_day * 24 * 3600 ) / 3600, 0 )
VAR s3_2_minute =
    ROUNDDOWN (
        ( s3_2_total_second - s3_2_total_day * 24 * 3600 - s3_2_hour * 3600 ) / 60,
        0
    )
VAR s3_2_second =
    ROUNDDOWN (
        ( s3_2_total_second - s3_2_total_day * 24 * 3600 - s3_2_hour * 3600 - s3_2_minute * 60 ),
        0
    )
VAR s3_2_time = s3_2_total_day & " day(s) " & s3_2_hour & ":" & s3_2_minute & ":" & s3_2_second


VAR s4_3_total_second =
    DATEDIFF ( s3, s4, SECOND )
VAR s4_3_total_day =
    ROUNDDOWN ( s4_3_total_second / 3600 / 24, 0 )
VAR s4_3_hour =
    ROUNDDOWN ( ( s4_3_total_second - s4_3_total_day * 24 * 3600 ) / 3600, 0 )
VAR s4_3_minute =
    ROUNDDOWN (
        ( s4_3_total_second - s4_3_total_day * 24 * 3600 - s4_3_hour * 3600 ) / 60,
        0
    )
VAR s4_3_second =
    ROUNDDOWN (
        ( s4_3_total_second - s4_3_total_day * 24 * 3600 - s4_3_hour * 3600 - s4_3_minute * 60 ),
        0
    )
VAR s4_3_time = s4_3_total_day & " day(s) " & s4_3_hour & ":" & s4_3_minute & ":" & s4_3_second


RETURN
    s4_3_time

vyingjl_0-1633572404502.png

 

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

mahoneypat
Microsoft Employee
Microsoft Employee

Please see these videos on how to model time and then calculate a total duration easily.  You should consider doing an unpivot of your data to simplify your analysis.

https://youtu.be/3Gy8uu3zmNI

https://youtu.be/v4DS9vHVgLE

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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