topic Re: Next working day calculation in DAX Commands and Tips

Next working day calculation

Uspace87 — Mon, 11 Sep 2023 14:26:15 GMT

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.

Re: Next working day calculation

johnt75 — Mon, 11 Sep 2023 14:53:07 GMT

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

Re: Next working day calculation

ThxAlot — Mon, 11 Sep 2023 17:17:22 GMT

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.

Re: Next working day calculation

Uspace87 — Wed, 20 Sep 2023 14:39:41 GMT

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