Forum Discussion
NETWORKDAYS with Holidays from different countries
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.
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?
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.
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.
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.