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

 

CountryCompleteByStartDateCompleteByEndDate
Korea, Republic of (South)11/16/201712/16/2017
Argentina5/9/20176/20/2017
Taiwan5/9/20176/20/2017
China6/30/20178/25/2017
USA7/1/20177/30/2017
Singapore2/16/20172/16/2017
China5/2/20176/30/2017
China5/12/20175/31/2017
China5/2/20176/30/2017
China4/27/20175/30/2017
China4/27/20175/31/2017
China5/16/20176/16/2017
Italy5/4/20176/3/2017

 

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

 

DateCountry
1/1/2017China
1/2/2017China
1/27/2017China
1/28/2017Argentina
1/29/2017Argentina
1/30/2017Argentina
1/31/2017Brazil
6/17/2017Brazil
6/20/2017Brazil
7/9/2017Japan
8/21/2017Japan
10/9/2017Japan
11/27/2017South Korea
12/8/2017South Korea
12/25/2017South 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?

Need Help

15 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

    • olimilo's avatar
      olimilo
      Post Prodigy
      9 years ago

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

       

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

       

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

         

  • PawelP's avatar
    PawelP
    New Member
    1 year ago
    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:

     

    CountryCompleteByStartDateCompleteByEndDate
    Korea, Republic of (South)11/16/201712/16/2017
    Argentina5/9/20176/20/2017
    Taiwan5/9/20176/20/2017
    China6/30/20178/25/2017
    USA7/1/20177/30/2017
    Singapore2/16/20172/16/2017
    China5/2/20176/30/2017
    China5/12/20175/31/2017
    China5/2/20176/30/2017
    China4/27/20175/30/2017
    China4/27/20175/31/2017
    China5/16/20176/16/2017
    Italy5/4/20176/3/2017

     

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

     

    DateCountry
    1/1/2017China
    1/2/2017China
    1/27/2017China
    1/28/2017Argentina
    1/29/2017Argentina
    1/30/2017Argentina
    1/31/2017Brazil
    6/17/2017Brazil
    6/20/2017Brazil
    7/9/2017Japan
    8/21/2017Japan
    10/9/2017Japan
    11/27/2017South Korea
    12/8/2017South Korea
    12/25/2017South 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])))​