cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

 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.

1 ACCEPTED SOLUTION
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``````

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.

3 REPLIES 3
Microsoft

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.

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!

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``````

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.

Microsoft

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!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors