NETWORKDAYS with Holidays from different countries

---

olimilo wrote:

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?

---

I had the same issue. My tables look almost the same and this worked for me:

Working Days = 
NETWORKDAYS(
Table1[CompleteByStartDate],Table1[CompleteByEndDate],1,
CALCULATETABLE(
VALUES(Table2[Date]),Table2[Country]=EARLIER(Table1[Country])))​