Forum Discussion

awitt's avatar
awitt
Helper III
6 years ago

Date Add With Working Day

Im looking to return a date that is a certain number of working days after a date in a date table. My date table has a record for each day and does already have a correct working day column. In this case I want a column that has 4 working days after each date represented. Any thoughts would be appreciated!

Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    awitt - So perhaps something like this:

     

    Column = 
  VAR __Date = 'Calendar'[Date]
  VAR __Dates = 
    ADDCOLUMNS(
      CALENDAR(__Date,__Date + 4),
      "Workday",WEEKDAY([Date],2)
    )
RETURN
  MAXX(FILTER(__Dates,[Workday] < 6),[Date]) + COUNTROWS(FILTER(__Dates,[Workday] > 5))

     

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi awitt 

    A bit of  a convoluted solution. I'm sure it can be done more efficiently and simply. It's a calculated column in your table, Table1, where Table1[Date] is the column you'd what to add the working days too.

    Calc colum =
VAR daysToAdd_ = 4 //update as required
RETURN
    MAXX (
        TOPN (
            daysToAdd_,
            CALCULATETABLE (
                DISTINCT ( CalendarTable[Date] ),
                CalendarTable[Date] > Table1[Date],
                CalendarTable[WorkingDay] = "Yes"
            ),
            CalendarTable[Date], ASC
        ),
        CalendarTable[Date]
    )

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers