adjust query to exclude holidays and weekends

[nony97]

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

[lbendlin]

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?