Calculate number of hours between 2 dates excluding Weekends

[Vivek26]

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 

[Anonymous · Accepted solution]

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

[IIPowerBlog]

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.

https://learn.microsoft.com/en-us/power-query/transpose-table