Calculate business hours and days

[Anonymous]

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

Id	Step 1 (Start)	Step 2	Step 3	Step 4 (End)
1	10/08/2021 10:47:32	10/08/2021 16:03:30	14/08/2021 12:12:08	15/08/2021 17:54:02
2	21/09/2021 12:30:41	22/09/2021 11:02:56	22/09/2021 12:56:01	30/09/2021 11:05:34
3	01/10/2021 18:01:01	03/10/2021 17:03:31	04/10/2021 08:57:01	04/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.

[mahoneypat]

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 ( result, 5 )

Pat

[v-yingjl · Accepted solution]

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

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]

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