Next working day calculation

[Uspace87]

Hi all,

I hope someone can help me 🙂

I would like to calculate a "Due date" having an "Order date"  without considering Weekends and Holidays.

I will give you a simple example:

Order date = 25/08/2023 (Friday). I need to add 3 working days so the final "Due date" would be 30/08/2023 (this is because I consider the 25, then skip 26 and 27 (Saturday and Sunday) and skip also 28 (Bank Holiday) and finally considering 29 and 30.

I am struggling to create a formula in dax or a calculated column.

Can someone help me?

thank you.

[ThxAlot]

Due date.pbix

On top of "old-school" way, newly-released WINDOW functions, OFFSET() to be exact here in your case, come into play in measures and calculated columns.

[Uspace87]

hi @ThxAlot ,

thank you for your help. The formula is working for any working day but if I want to offset a "Saturday", It does return me "Blank".

For thjs order created on saturday the deadline should be next monday but the offset formula is returning me Blank.

Please find below the formula I am using:

Due_date CC =

VAR _duration =

SWITCH(

    TRUE(),

    'Interim Sales Header PS +PSF'[Duration]=0,'Interim Sales Header PS +PSF'[Duration],

    'Interim Sales Header PS +PSF'[Duration]=1,'Interim Sales Header PS +PSF'[Duration],

    'Interim Sales Header PS +PSF'[Duration]=2,'Interim Sales Header PS +PSF'[Duration]-1,

    'Interim Sales Header PS +PSF'[Duration]=3,'Interim Sales Header PS +PSF'[Duration]

)

Var result =

MAXX(

    OFFSET(

     _duration,

        FILTER(

            ALLSELECTED(Date_Table),

         Date_Table[Working day?]=1

        ),

        ORDERBY(Date_Table[Date])

        ),

        Date_Table[Date]

    )

    return result

[johnt75]

Have a look at https://www.youtube.com/watch?v=2HkBbqxBzF0.