cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

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.

 

1 ACCEPTED SOLUTION
Super User III
Super User III

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,Screenshot 2020-12-05 125737.png

 

DAX solution

Screenshot 2020-12-05 125815.png

 

View solution in original post

3 REPLIES 3
Super User III
Super User III

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

Frequent Visitor

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

Super User III
Super User III

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,Screenshot 2020-12-05 125737.png

 

DAX solution

Screenshot 2020-12-05 125815.png

 

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors