Forum Discussion

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost 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
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
    Icon for Solution Sage rankSolution 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
      Icon for Solution Sage rankSolution 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))

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        JoeBarry
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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