Forum Discussion

olimilo's avatar
olimilo
Post Prodigy
9 years ago

NETWORKDAYS with Holidays from different countries

Hi guys, this is a bit of a challenge for me and I'm hoping someone can help lead me in the right direction.   I have a Records table that I need to check for the # of working days from StartDate t...
Need Help
JoeBarry's avatar
JoeBarry
Solution Sage
3 years ago

There is a really good API I use to get all Public Holidays, but it's quite restricted to calling one year and one country at the same time.

 

Info can be found here.

https://date.nager.at/Api

 

If your calling in Power BI, open up the Web connector and enter the URL like this 

 

I usually build a few years for one country in a dataflow and then append them into one Table 

I then merge with my date table on the date column to have a column Holidays US for example. 

 

To solve the problem above. Make a One to Many relationship from the Date column in the Date Table to the completed by startdate in the table.

 

 

 

 

Holiday Days in Timeframe =

CALCULATE(
   COUNT('DIM Date'US_Holiday]),
     FILTER('DIM Date', 'DIM Date'[Date] >= Records[CompleteByStartDate] &&
         'DIM Date'[Date] < Records[CompleteByEndDate] && 'DIM Date'[US_Holiday] <> BLANK()))

 

 

 

 

Once you have this you can get the days between and then minus the amount of holidays

 

 

 

Days = DATEDIFF(Records[CompleteByStartDate], Records[CompleteByEndDate], DAY)- [Holidays Days in Timeframe]

 

 

 

 

Hope this helps 

Joe

If this post helps, then please Accept it as the solution 

JoeBarry's avatar
JoeBarry
Solution Sage
3 years ago

If you create a table just with all country holidays as above with a Country column (prefrablly a country id would be better) The below measure should work also

 

Holiday Days in Timeframe =

CALCULATE(
   COUNT(Holidays[Holiday]),
     FILTER('Holidays', 'Holidays'[Date] >= Records[CompleteByStartDate] &&
         'Holidays'[Date] < Records[CompleteByEndDate] && 'Holidays'[Country] = Records[Country))