topic Re: Adding working days to date in DAX Commands and Tips

Adding working days to date

jimpatel — Tue, 16 Jul 2024 09:46:50 GMT

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 )

Re: Adding working days to date

bhanu_gautam — Tue, 16 Jul 2024 10:04:29 GMT

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
)

Re: Adding working days to date

Anonymous — Wed, 31 Jul 2024 08:42:34 GMT

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.