Calculate DATEDIFF in Hours between 2 date-time columns - Exclude weekends and time between 5PM-8AM

[tyatsenko]

Hi,

I have table with 3 columns Project ID, Start Date (Date-Time format) and End Date (Date Time Format).

I need to calculate duration of each project in hours, excluding weekends and after work hours time (between 5PM and 8AM).

For example, 

Start Date=6/20/22 9:18AM, Wed

End Date=2/1/23 9:58AM, Wed

1. Duration of the project on start date will be from 9:18 AM to 5PM  = 7 h 42 min

2. There are 163 working days between start and End date, therefore 163*8=1304 h

3. Working Hours during End date will be 9:58AM-8AM=1h 58Min

Total time between End nad start should be sum of 7h 42min+1304H+1h 58min=1313h 40min

How can I replicate this calculation in Power Bi?

Thanks,

[amitchandak · Accepted solution]

@tyatsenko , Try this https://exceleratorbi.com.au/calculating-business-hours-using-dax/

[tyatsenko]

@amitchandak 

This is perfect! Problem solved. Thanks a lot!