Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Create measure between two timestamps, only count working hours

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 8...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

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.