Create measure between two timestamps, only count working hours

[Anonymous]

Hi,

I have created measures to find timestamps for some comditions. I would like to add a measure to see the number of working hours between the two timestamps.
I have defined working hours as 8am-4pm.
Some timestamps can be outside working hours, then I would like the time to start the following day at 8am.

For instance the time between 24.07.2020 21:36:19 and 25.07.2020 00:28:01 should be 0 hours or 0 minutes.

I have a calendar table to define workdays and a timetable to define working hours. 

Right now I'm using datediff to find hours between two measures. I have been able to figure out days using this formula:

= DATEDIFF([Measure1], [Measure2], DAY) -
     (CALCULATE(
        COUNTROWS('Calendar'),
            'Calendar'[Workday] = FALSE(),
            DATESBETWEEN('Calendar'[Date'], [Measure1], [Measure2])
            )
  )

This is working for days, but struggling with hours and working hours.

Thanks!

[lbendlin]

Careful what you are getting yourself into.  

First of all, what is your timezone?  Keep in mind the Power BI Service works on UTC.

Here are the steps you need to take:

- identify if the start timestamp is on a workday. If yes, find the difference in minutes between the start timestamp and the end of business hours (only consider it if not negative!). If no the number is zero.

- identify if the end timestamp is on a workday. If yes, find the difference in minutes between start of business hours and the end timestamp (again only if not negative!). If no then zero

- calculate the number of workdays AFTER your start timestamp day and BEFORE your end timestamp day.  Exclude non-workdays (weekends, holidays).  Multiply by 480 and add to the two other numbers.