Net Working Days between dates - Multiple Countries with Holidays - Different Working Days per week

[dearears]

Hello All, Looking to get some help on this situation.

Objective is to get Networkdays between [startdate] and [enddate] based on weekly roster and holidays mapped for a [locationcode]

Main Table

[startdate]    [enddate]    [locationcode]

1 Jan 2020   6 Jan 2020      4195

5 Jan 2020    9 Jan 2020     2000

6 Jan 2020    14 Jan 2020   5600

....

Holidays Table

[locationcode]    [holidaydate]

4195                    4 Jan 2020

2000                    8 Jan 2020 

.....

Additional Info for Holidays Table: 5600 does not have any holidays hence no mention in this table.

WeeklyRoster Table

[locationcode]        [weekdaynotworking]

4195                 5

4195                 7

2000                 6

....

Additional Info for Weekly Roster Table:

1. Mapped as Day number of week, 1 being Monday.

2. Location 5600 works all 7 days hence not mention in this table.

Many thanks in advance.

[CNENFRNL]

So glad my solution is of help and it's kind of you to say so!

[dearears]

There is a good reason why you have earned that Badge of Resident Rockstar ! Thanks for your help on this.

[CNENFRNL · Accepted solution]

Hi, @dearears , the underlying logic is very simple, i.e. excluding holidays and non-working weekdays from assigned period; but the procedure is fairly verbose. I came up with a PQ solution and a DAX solution. You might want to refer to the attached file for details.

PQ solution,

DAX solution