Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
Solved! Go to 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.
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.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |