topic Re: Calculating elapsed business hours in DAX Commands and Tips

Calculating elapsed business hours

Powerbiuser667 — Thu, 30 Nov 2023 19:41:09 GMT

Who can help me with this formula?

I’m trying to calculate the business hours that have elapsed between the moment a record has been opened and the moment that record is closed. After receiving error after error I finally have a code that doesn’t give me an error, but is still not showing me the correct numbers. What should the code look like? I'm clearing doing something very wrong (not very experienced yet).

This is what I have

NetWorkHours =
VAR BusinessStartHour = TIME(8;00;00) // Set the start of business hours (8:00 AM)
VAR BusinessEndHour = TIME(18, 0, 0) // Set the end of business hours (6:00 PM)
VAR StartDate = [OpenedDateTime]
VAR EndDate = [ClosedDateTime]

VAR NetWorkDays = NETWORKDAYS(StartDate, EndDate)

VAR StartWorkHours = IF(TIME(HOUR(StartDate), MINUTE(StartDate), SECOND(StartDate)) < BusinessEndHour, BusinessEndHour - TIME(HOUR(StartDate), MINUTE(StartDate), SECOND(StartDate)), 0)
VAR EndWorkHours = IF(TIME(HOUR(EndDate), MINUTE(EndDate), SECOND(EndDate)) > BusinessStartHour, TIME(HOUR(EndDate), MINUTE(EndDate), SECOND(EndDate)) - BusinessStartHour, 0)

VAR NetWorkHours =
IF(
NetWorkDays > 0,
(NetWorkDays * (BusinessEndHour - BusinessStartHour)) + StartWorkHours + EndWorkHours,
IF(
StartDate >= EndDate || StartDate = EndDate,
0,
StartWorkHours + EndWorkHours
)
)

RETURN
NetWorkHours // Returns the net work hours without rounding

Re: Calculating elapsed business hours

123abc — Fri, 01 Dec 2023 09:44:51 GMT

Here is modified Dax Measure:

NetWorkHours =
VAR BusinessStartHour = TIME(8, 0, 0) // Set the start of business hours (8:00 AM)
VAR BusinessEndHour = TIME(18, 0, 0) // Set the end of business hours (6:00 PM)
VAR StartDate = [OpenedDateTime]
VAR EndDate = [ClosedDateTime]

VAR NetWorkDays = NETWORKDAYS(StartDate, EndDate)

VAR StartWorkHours =
IF(
TIME(HOUR(StartDate), MINUTE(StartDate), SECOND(StartDate)) < BusinessStartHour,
BusinessStartHour - TIME(HOUR(StartDate), MINUTE(StartDate), SECOND(StartDate)),
0
)

VAR EndWorkHours =
IF(
TIME(HOUR(EndDate), MINUTE(EndDate), SECOND(EndDate)) > BusinessEndHour,
TIME(HOUR(EndDate), MINUTE(EndDate), SECOND(EndDate)) - BusinessEndHour,
0
)

VAR NetWorkHours =
IF(
NetWorkDays > 0,
(NetWorkDays - 1) * (BusinessEndHour - BusinessStartHour) + StartWorkHours + EndWorkHours,
IF(
StartDate < EndDate && StartDate <> EndDate,
StartWorkHours + EndWorkHours,
0
)
)

RETURN
NetWorkHours // Returns the net work hours without rounding

Re: Calculating elapsed business hours

Anonymous — Tue, 05 Dec 2023 05:46:41 GMT

Hi Powerbiuser667 ,

Have you solved your problem? If solved please mark the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!
If the problem hasn't been solved yet, could you please provide the results you're getting with the DAX you're using and what you're hoping to get, that would be helpful!

Best Regards,
Dino Tao