cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dearears
Helper I
Helper I

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
CNENFRNL
Super User
Super User

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

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

3 REPLIES 3
CNENFRNL
Super User
Super User

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

dearears
Helper I
Helper I

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

CNENFRNL
Super User
Super User

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

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors