Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.