cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate number of hours between 2 dates excluding Weekends

I have a specific scenario where I need to calculate the number of hours between 2 dates excluding Weekends.

we get orders that move through different stages and we want to calculate how much time each stage is taking on an average.

For example , for an order the step 1 completed on  30th Dec' 2022 at 11 PM

while the step 2nd completed on  2nd Jan 2022 at 8 AM.

The time taken for step 2 should be shown as 9 hours ( Excluding Saturday and Sunday ) and not 57 hours.

This should be accounted for while calculating all averages.

I really appreciate the help here

1 ACCEPTED SOLUTION
Community Support

Hi @Vivek26 ,

Assuming that the dates of all steps are workdays, try this:

``````Hours =
VAR _time_1 =
CALCULATE (
MAX ( 'Table'[Status Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Order NO] = EARLIER ( 'Table'[Order NO] )
&& 'Table'[Step] < EARLIER ( 'Table'[Step] )
)
)
VAR _hours =
IF (
ISBLANK ( _time_1 ),
0,
DIVIDE ( DATEDIFF ( _time_1, 'Table'[Status Date], SECOND ), 3600 )
- MAX (
DATEDIFF ( _time_1, 'Table'[Status Date], DAY ) + 1
- NETWORKDAYS ( _time_1, 'Table'[Status Date] ),
0
) * 24
)
RETURN
_hours``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 REPLIES 2
Community Support

Hi @Vivek26 ,

Assuming that the dates of all steps are workdays, try this:

``````Hours =
VAR _time_1 =
CALCULATE (
MAX ( 'Table'[Status Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Order NO] = EARLIER ( 'Table'[Order NO] )
&& 'Table'[Step] < EARLIER ( 'Table'[Step] )
)
)
VAR _hours =
IF (
ISBLANK ( _time_1 ),
0,
DIVIDE ( DATEDIFF ( _time_1, 'Table'[Status Date], SECOND ), 3600 )
- MAX (
DATEDIFF ( _time_1, 'Table'[Status Date], DAY ) + 1
- NETWORKDAYS ( _time_1, 'Table'[Status Date] ),
0
) * 24
)
RETURN
_hours``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Helper I

hi @Vivek26  have you tried to revert the Step values (rows) to a column? this will provide you with the opportuinity to create a measure or a calculated field with a Date Difference between steps, according to your needs.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors