Forum Discussion

jimpatel's avatar
jimpatel
Post Patron
2 years ago
Solved

Adding working days to date

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
)

 

"Need Help"
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.