Replicating WORKDAY formula with holidays per country

BenzeBenze — Tue, 23 Jan 2024 13:37:05 GMT

Dear Community,

My name is Borja and I am currently working as a BI specialist in one european logistics company (which I prefer to not mention).

Basically, we have sold some logistic services to a customer, and we have a signed SLA agreement with the customer depending on the country. What I am trying to build is a column that gives me the Guaranteed Delivery Date for the parcels depending on when they were processed and where are they going to.

For example lets assume a parcel processed on January 23th going to UK has a SLA of 4 days. This means it should be delivered in 4 working days, therefore the Guaranteed Delivery Date should be January 29th.

The first problem I am facing is how to ignore weekends, in Excel I use WORKDAY formula but this doesn't exist in Power BI.

My second issue is how to also take into account bank holidays per country. In Excel we use a FILTER formula so it only adds the bank holidays for the destination country.

Oue formula looks like this =WORKDAY(K2,Y2,FILTER(Holidays!A1:X27, Holidays!A1:X1=E2))

Column K is the Processed date.

Column Y is the agreed SLA.

Holidays!A1:X27 is the sheet with all the holidays per country.

Column E is the country.

Does anyone knows how to build this in PowerBI?

Thanks in advance.

Re: Replicating WORKDAY formula with holidays per country

vicky_ — Wed, 24 Jan 2024 02:36:11 GMT

The equivalent function in PowerBI is networkdays: https://learn.microsoft.com/en-us/dax/networkdays-dax

you'll need another table of dates with the holidays per country to pass a the final argument to the function.

Re: Replicating WORKDAY formula with holidays per country

Benzebenze2 — Wed, 24 Jan 2024 10:05:27 GMT

Hi Vicky,

Thanks for your kind answer.

As far as I know, NETWORKDAYS will give you the number of working days between two days, and what I need is to get the resulting date after adding X working days to a date.

topic Replicating WORKDAY formula with holidays per country in DAX Commands and Tips

Replicating WORKDAY formula with holidays per country

Re: Replicating WORKDAY formula with holidays per country

Re: Replicating WORKDAY formula with holidays per country