Forum Discussion
Post ProdigyNETWORKDAYS with Holidays from different countries
Solution SageThere 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.
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
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
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