Calculating elapsed business hours

[Powerbiuser667]

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

[Anonymous]

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

[123abc]

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