Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How exclude holidays from networking days

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('Quer...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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