Forum Discussion
NETWORKDAYS with Holidays from different countries
Create a Date Table, that contains each day. Join that Date table to your other 2 tables via their date columns.
Hi Anonymous, I think that's only possible if the dates do not repeat. If 3 countries celebrate a holiday on 1/1/2017:
| Date | Country |
| 1/1/2017 | USA |
| 1/1/2017 | China |
| 1/1/2017 | Taiwan |
| 1/1/2017 | Japan |
| 1/1/2017 | Brazil |
| 1/2/2017 | Brazil |
| ... | ... |
| 12/31/2017 | USA |
| 12/31/2017 | Brazil |
| 12/31/2017 | Japan |
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.
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.