How exclude holidays from networking days

[Anonymous]

Hello Community,

I have created networkdays between two days (it includes holidays and excludes weekends) by using this:

NetWorkDaysEND =

VAR Calendar1 = CALENDAR(MIN('Query1'[END]),MAX('Query1'[FYEND]))

VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))

RETURN

COUNTX(FILTER(Calendar2,[WeekDay]<=7),[Date])- COUNTX(FILTER(Calendar2,[WeekDay]>=6),[Date])

But now i have holidays table with DAYName, Date, Holiday Columns.... So, i want to exclude holidays also 

Can you guys please help me out from this

Thank you in advance

Regards,

B V S S

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

we can create such a measure to meet your requirement.

NetWorkDaysENDExcludeHoliday = 
VAR Calendar1 =
    CALENDAR ( MIN ( 'Query1'[END] ), MAX ( 'Query1'[FYEND] ) )
VAR Calendar2 =
    ADDCOLUMNS ( Calendar1, "WeekDay", WEEKDAY ( [Date], 2 ) )
RETURN
    SUMX (
        FILTER ( Calendar2, [WeekDay] <= 5 ),
        IF ( CONTAINS ( holidays, 'holidays'[Date], [date] ), 0, 1 )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[Anonymous]

Hi @v-lid-msft ,

Thank you for your solution.. It's working fine for my data

[Anonymous]

Hello guys,

Any Suggestions Please... 

Thank you in advance

Regards,

B V S S

[jdbuchanan71]

Hello @Anonymous 

I have attached a sample file where my Dates table has a column "IsWorkingDay" that is = FALSE on weekends and holidays.  Using that and the dates you are comparing you can just do a count of rows with some filtering.

WorkingDays = 
VAR LowEnd =
    FIRSTDATE ( 'Table'[StartDate] )
VAR HighEnd =
    LASTDATE ( 'Table'[EndDate] )
RETURN
    CALCULATE (
        COUNTROWS ( Dates ),
        Dates[Date] >= LowEnd && Dates[Date] <= HighEnd,
        Dates[IsWorkingDay] = TRUE
    )

[Anonymous]

Hi @jdbuchanan71 ,

Thank you for your response.. I tried but it's not working properly for my case. May be some other cases it will work