Adding working days to date

[jimpatel]

Hi,

Thanks for looking at my post.

I am using below formula but for somereason it is not working correctly. What i am looking for is i wanted to add 2 working days extra to "Date" column. If Date fall on 11th july (thursday) then new date will be 15th July (Monday) due to weekend inbetween. Any idea please?

Thanks a lot

 Table1

Due Date = SWITCH(TRUE(),
         WEEKDAY( 'Table1'[Date] + 1,1) = 7 , 'Table1'[Date] + 4, 'Table1'[Date] + 2
)

[bhanu_gautam · Accepted solution]

@jimpatel , Try using below measure

Due Date =
VAR CurrentDate = 'Table1'[Date]
VAR DayOfWeek = WEEKDAY(CurrentDate, 2) -- 2 means Monday=1, Tuesday=2, ..., Sunday=7
RETURN
SWITCH(
TRUE(),
DayOfWeek <= 3, CurrentDate + 2, -- Monday to Wednesday
DayOfWeek = 4, CurrentDate + 4, -- Thursday
DayOfWeek = 5, CurrentDate + 4, -- Friday
DayOfWeek = 6, CurrentDate + 4, -- Saturday
DayOfWeek = 7, CurrentDate + 3 -- Sunday
)

[Anonymous · Accepted solution]

Hi @jimpatel ,

I create a table as you mentioned. Then I create a calculated column and here is the DAX code.

NewDate = 
VAR StartDate = 'Table'[Date]
VAR DaysToAdd = 2
VAR WeekdayNumber =
    WEEKDAY ( StartDate, 2 )
VAR DaysAdded =
    SWITCH (
        TRUE (),
        WeekdayNumber + DaysToAdd <= 5, DaysToAdd,
        WeekdayNumber + DaysToAdd > 5, DaysToAdd + 2
    )
RETURN
    StartDate + DaysAdded

Finally I will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam · Accepted solution]

@jimpatel , Try using below measure

Due Date =
VAR CurrentDate = 'Table1'[Date]
VAR DayOfWeek = WEEKDAY(CurrentDate, 2) -- 2 means Monday=1, Tuesday=2, ..., Sunday=7
RETURN
SWITCH(
TRUE(),
DayOfWeek <= 3, CurrentDate + 2, -- Monday to Wednesday
DayOfWeek = 4, CurrentDate + 4, -- Thursday
DayOfWeek = 5, CurrentDate + 4, -- Friday
DayOfWeek = 6, CurrentDate + 4, -- Saturday
DayOfWeek = 7, CurrentDate + 3 -- Sunday
)