NETWORKDAYS with Holidays from different countries

[olimilo]

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 to EndDate minus the number of holidays that occur within that time period based on the Country column of that same table. For example:

Country	CompleteByStartDate	CompleteByEndDate
Korea, Republic of (South)	11/16/2017	12/16/2017
Argentina	5/9/2017	6/20/2017
Taiwan	5/9/2017	6/20/2017
China	6/30/2017	8/25/2017
USA	7/1/2017	7/30/2017
Singapore	2/16/2017	2/16/2017
China	5/2/2017	6/30/2017
China	5/12/2017	5/31/2017
China	5/2/2017	6/30/2017
China	4/27/2017	5/30/2017
China	4/27/2017	5/31/2017
China	5/16/2017	6/16/2017
Italy	5/4/2017	6/3/2017

On that table, I have at least 7 countries. Now, I made a Holidays table:

Date	Country
1/1/2017	China
1/2/2017	China
1/27/2017	China
1/28/2017	Argentina
1/29/2017	Argentina
1/30/2017	Argentina
1/31/2017	Brazil
6/17/2017	Brazil
6/20/2017	Brazil
7/9/2017	Japan
8/21/2017	Japan
10/9/2017	Japan
11/27/2017	South Korea
12/8/2017	South Korea
12/25/2017	South Korea

However, when I try to create a relationship between the Records table and the Holidays table, PBI won't let me because it requires a unique identifier for at least one table between the two (for this context, it should be the Holiday table). I've also been looking at several posts on the forums and it seems like it only deals with holidays from one country. I'm wondering if it is possible to do this on a multiple country basis in Power BI?

[Clem_Fandango]

For anyone finding this in the future, this post may help:
Calculate number of working days for each company - Microsoft Fabric Community

[Clem_Fandango]

6 years on and I'm struggling to find a solution to this too.  Even ChatGPT can't help. 😄

I'm wondering if the solution is multiple "Is Holiday" columns added to the DimDate table in.  eg 1 for each country. "Is Holiday - China", "Is Holiday - Australia", etc.

Quite how we'd go about creating those columns, I'm not sure.

I'm shouting into the void, hoping that someone will have a solution! Please? 🙂 

[JoeBarry]

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 

https://date.nager.at/api/v3/publicholidays/2023/US Filter out non day off days

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 

[Clem_Fandango]

Thanks Joe,

I'm OK with getting public holidays for 1 country via an API and then merging it with my Date Dimension table to create an "Is Holiday" type column.  

The issue I have is I'm now building a Date Dimension table in a Data Warehouse that will be used in multiple countries and not sure how that will work in practice.

Thinking out loud, I'm thinking we would have an "Is Holiday" column in the DimDate table for each country.  As long as reports are filtered to 1 country through RLS or explicitly we could have dynamic measures in the dataset file doing something like:

Holiday Days in Timeframe = 

var _country = SELECTEDVALUE('Dim Country') 
var _holiday_column = "'DIM Date'[Is Holiday " & _country & "]" 
//(Not sure the above is even possible in DAX) 

var _result = 

CALCULATE( 
DISTINCTCOUNTNOBLANK(_holiday_column), 
FILTER('DIM Date', 'DIM Date'[Date] >= Records[CompleteByStartDate] 
&& 'DIM Date'[Date] < Records[CompleteByEndDate])) 

return _result

[JoeBarry]

I think it would be easier to just create a Public holiday table with Date, Country, CountryID, HolidayName columns. 

Adding a column to the date table can get messy, whereas, if you have one table, more countries can be added later without adding more columns.

The 2nd solution I created would then be useful. You can do RLS then on a DIM_Country table which has a one to many relationship with both the Holiday Table and the Records table

[Clem_Fandango]

Thanks Joe. 
You're right,  using a DIM_Holiday_Country table (that will only ever have 1 country selected; either through a slicer or via RLS) that filters our Dim_Holidays (but not our Fact table) is our best shout I think.
That way, the user (or RLS) defines which country we're using for our Public Holidays in any measures related to working days.  We can then have Executive level report users still being able to view all countries in the report because we're not filtering the Fact Table.

I'm then going to create a calculated Is_Holiday column using DAX (LOOKUPVALUE) on my DimDate table to bring in just the filtered holidays in my DimHolidays table.

Funnily enough I came to the same conclusion as you after a long conversation with ChatGPT. 🙂 

Thanks for your help,
Clem

[JoeBarry]

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))

[Anonymous]

Create a Date Table, that contains each day.  Join that Date table to your other 2 tables via their date columns.

[olimilo]

Hi @Anonymous, I think that's only possible if the dates do not repeat. If 3 countries celebrate a holiday on 1/1/2017:

Date	Country
1/1/2017	USA
1/1/2017	China
1/1/2017	Taiwan
1/1/2017	Japan
1/1/2017	Brazil
1/2/2017	Brazil
...	...
12/31/2017	USA
12/31/2017	Brazil
12/31/2017	Japan

This will make me unable to use the table since both columns have repeating values. I am unable to use a Holiday Flag column too since I'm working with holiday listings of more than 1 country.

[Anonymous]

The date table will definately work because the Date Table is just every date possible.  There is only 1 occurance of any given date in this table.  Its something referred to as a Date Dimension Table.  The dates repeating in your existing tables is fine.

[olimilo]

I get what you're trying to say but how would you supply the countries that have a holiday? Let's say on 1/1/2017, 10 countries celebrate a holiday on that day. Would you be willing to provide a sample on how the data structure would look like?

[Anonymous]

Its really going to come down to how you write your measures.  For example you might have a measure that needs to count the number of holidays in your holiday table.  This measure might be part of a Matrix, thus there is already a date range and country filter.  Thus when the measure is run, it can only see "Dates" in your range and the country name.

On the other way around. If you were using a measure to "Count the number of countries on holiday", you might be doing this via a filter that uses a single date.  By doing this, the measure will only see that day and return the count.

[Anonymous]

Here is an alternate method if you just want to count the public holidays:

Create a custom column in your Project Table and use this:

PubHol Count = Calculate(
	count('PubHol'[Date]),
	'PubHol'[Country] = [Country],
	'PubHol'[Date] >= [CompleteByStartDate],
	'PubHol'[Date] <= [CompleteByEndDate]
)

You will need to up date the table name, i've guessed it.

[olimilo]

Okay, so this will be the first time I'll be working with a lot of dates, hope you bear with me here. Is this how the Date dimension table would look like?

Now I'm wondering how I can incorporate the holidays on each country to another table that I can create a relationship with this table.