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('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
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

5 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-lid-msft ,

       

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello guys,

     

    Any Suggestions Please... 

     

    Thank you in advance

     

     

    Regards,

    B V S S

    • 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