adjust query to exclude holidays and weekends

nony97 — Thu, 24 Oct 2024 07:50:36 GMT

i wrote this query that calulate work hours (8:30 to 16:30) and need it to exclude holidayes ( i have a seperate holiday table that has holiday start date and holiday end date) and i need it to exclude weekends (friday and saturday)

here is the dax power query

workhours without out of working hours =

VAR WorkTimeStart = TIME ( 08, 30, 00 )

VAR WorkTimeEnd = TIME ( 16, 30, 10 )

VAR WorkingHours = ( WorkTimeEnd - WorkTimeStart )

-- Start and End date/time on current row

VAR StartingDateTime = [Created - Copy]

VAR EndingDateTime = [Completed - Copy]

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

Re: adjust query to exclude holidays and weekends

lbendlin — Thu, 24 Oct 2024 12:27:52 GMT

need it to exclude holidayes ( i have a seperate holiday table that has holiday start date and holiday end date) and i need it to exclude weekends (friday and saturday)

Have you considered using the standard NETWORKDAYS DAX function?

topic adjust query to exclude holidays and weekends in DAX Commands and Tips

adjust query to exclude holidays and weekends

Re: adjust query to exclude holidays and weekends