Forum Discussion

hasharma19's avatar
hasharma19
Helper II
3 years ago

Dates table Issue (Weekend, Weekdays, holiday)

HI All,

I have two dates table. One is of Holiday list and aother is of request date.

I am looking for solution where the request date match with holiday dates so request date considered  from next weekday.

Similarly if request assigned on weekend so request date next weekday.

Looking for help.

 

 

Thanks

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    hasharma19 Maybe something like:

    Column = 
  VAR __DateTime = [Requested Date & Time]
  VAR __Date = DATE( YEAR(__DateTime), MONTH(__DateTime), DAY(__DateTime) )
  VAR __Holidays = SELECTCOLUMNS( 'Holiday List', "Date", [Holiday Date] )
  VAR __Calendar = 
    ADDCOLUMNS(
      EXCEPT(
        CALENDAR( __Date, __Date + 7 ),
        __Holidays
      ),
      "__WeekDay", WEEKDAY([Date], 2)
    )
  VAR __NextWorkDay = MINX( FILTER( __Calendar, [__WeekDay] < 6 ), [Date] )
  VAR __Result = IF( WEEKDAY( __Date, 2 ) > 5 || __Date IN __Holidays, __NextWorkDay, __Date )
RETURN
  __Result