Forum Discussion
adjust query to exclude holidays and weekends
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
1 Reply
- lbendlin
Super User
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?
