hour difference without workhours

[mina97]

i have the following data 

start date	end date	status
2023-07-02 6:01:00	2023-07-04 15:10:11	confirmed

1- i want to calculate the time difference in hours only if it is between 8:00:00 and 17:10:00 so the example upove should be 25 hours

2- and i want the same thing but in minutes 

please help me and thank you  

[OwenAuger · Accepted solution]

Hi @mina97 

I have attached a PBIX with suggested calculated colums in DAX for Work Hours and Work Minutes.

The code is a bit long-winded in order to capture the required logic.

I assumed:

• Weekends/holidays do not need to be excluded. If you do, you would need to adjust and use NETWORKDAYS.
• You want to round down to the nearest integer.

Here is the expression for Work Hours (Work Minutes is similar):

Work Hours = 
-- Working Start and End time 
VAR WorkTimeStart = TIME ( 08, 00, 00 )
VAR WorkTimeEnd = TIME ( 17, 10, 10 )
VAR WorkingHours = ( WorkTimeEnd - WorkTimeStart )

-- Start and End date/time on current row
VAR StartingDateTime = Data[start date]
VAR EndingDateTime   = Data[end date]

VAR StartingTime= StartingDateTime - TRUNC ( StartingDateTime )
VAR StartingDate = StartingDateTime - StartingTime

VAR EndingTime = EndingDateTime - TRUNC ( EndingDateTime )
VAR EndingDate = EndingDateTime - EndingTime

-- Adjust start/end times to fall within working hours.
VAR StartingTimeEffective =
    MIN (
        MAX ( StartingTime, WorkTimeStart ),
        WorkTimeEnd
    )
VAR EndingTimeEffective =
    MAX (
        MIN ( EndingTime, WorkTimeEnd ),
        WorkTimeStart
    )
-- Adjust for hours not worked on StartingDate
--   StartingTimeOffset will always be <= 0
VAR StartingTimeOffset =
    WorkTimeStart - StartingTimeEffective
-- Adjust for hours not worked on EndingDate
--   EndingTimeOffset will always be <= 0
VAR EndingTimeOffset =
    EndingTimeEffective - WorkTimeEnd
VAR DayCount =
    EndingDate - StartingDate + 1
VAR TotalTimeInDays =
    DayCount * WorkingHours + StartingTimeOffset + EndingTimeOffset
VAR TotalTimeInHours =
    TotalTimeInDays * 24
VAR TotalTimeInHoursRounded =
    ROUNDDOWN ( TotalTimeInHours, 0 )
RETURN
    TotalTimeInHoursRounded

This is a good article on a similar but different calculation:

https://www.sqlbi.com/blog/alberto/2019/03/25/using-dax-with-datetime-values/

Does this work for you?

Regards

[OwenAuger · Accepted solution]

Hi @mina97 

Glad it helped.

The expression does just count working hours.

The variable TotalTimeInDays does just include working hours, with an adjustment for the start/end times.

But let me know if you have a specific example where it is not working.

Regards

[OwenAuger · Accepted solution]

Hi @mina97 

I have attached a PBIX with suggested calculated colums in DAX for Work Hours and Work Minutes.

The code is a bit long-winded in order to capture the required logic.

I assumed:

• Weekends/holidays do not need to be excluded. If you do, you would need to adjust and use NETWORKDAYS.
• You want to round down to the nearest integer.

Here is the expression for Work Hours (Work Minutes is similar):

Work Hours = 
-- Working Start and End time 
VAR WorkTimeStart = TIME ( 08, 00, 00 )
VAR WorkTimeEnd = TIME ( 17, 10, 10 )
VAR WorkingHours = ( WorkTimeEnd - WorkTimeStart )

-- Start and End date/time on current row
VAR StartingDateTime = Data[start date]
VAR EndingDateTime   = Data[end date]

VAR StartingTime= StartingDateTime - TRUNC ( StartingDateTime )
VAR StartingDate = StartingDateTime - StartingTime

VAR EndingTime = EndingDateTime - TRUNC ( EndingDateTime )
VAR EndingDate = EndingDateTime - EndingTime

-- Adjust start/end times to fall within working hours.
VAR StartingTimeEffective =
    MIN (
        MAX ( StartingTime, WorkTimeStart ),
        WorkTimeEnd
    )
VAR EndingTimeEffective =
    MAX (
        MIN ( EndingTime, WorkTimeEnd ),
        WorkTimeStart
    )
-- Adjust for hours not worked on StartingDate
--   StartingTimeOffset will always be <= 0
VAR StartingTimeOffset =
    WorkTimeStart - StartingTimeEffective
-- Adjust for hours not worked on EndingDate
--   EndingTimeOffset will always be <= 0
VAR EndingTimeOffset =
    EndingTimeEffective - WorkTimeEnd
VAR DayCount =
    EndingDate - StartingDate + 1
VAR TotalTimeInDays =
    DayCount * WorkingHours + StartingTimeOffset + EndingTimeOffset
VAR TotalTimeInHours =
    TotalTimeInDays * 24
VAR TotalTimeInHoursRounded =
    ROUNDDOWN ( TotalTimeInHours, 0 )
RETURN
    TotalTimeInHoursRounded

This is a good article on a similar but different calculation:

https://www.sqlbi.com/blog/alberto/2019/03/25/using-dax-with-datetime-values/

Does this work for you?

Regards

[mina97]

but you did not exclude the out of working hours 

[OwenAuger · Accepted solution]

Hi @mina97 

Glad it helped.

The expression does just count working hours.

The variable TotalTimeInDays does just include working hours, with an adjustment for the start/end times.

But let me know if you have a specific example where it is not working.

Regards

[mina97]

your work is amazing !! but how to shift to calculate minutes 

[OwenAuger]

🙂

The PBIX attached earlier has a "Work Minutes" calculated column that is essentially Work Hours * 60.

[mina97]

i can not download the file 😞

[OwenAuger · Accepted solution]

That's odd - it's in my post above, but I reattached here in case that helps.

Here is the link created by the forum:

https://community.fabric.microsoft.com/oxcrx34285/attachments/oxcrx34285/DAXCommands/131682/1/Work%2...

[mina97]

thank you so much

[mina97]

thank you it helped