Regional and Country Specific Public Holidays

[Jim_O]

Hi guys,

I'm looking for a "Sense" check 😉 on the following, please:

Scenario
I have a table containing a drill down dimension of Region-->Country.
I have a calculated measure that gives number of weekdays MTD, coming from a Master Calendar.

Requirement
At the Region level of drill, provide number of workdays (weekdays minus holiday)
At the Country level of drill, provide number of workdays(weekdays minus country specific holidays)

Possible Solution
My initial thoughts are to add columns to my Master Calendar, one for Group (shared holidays such as Christmas, New Year and Easter) and then other columns (one per country) that have the same data for those shared holidays, plus flags for any country specific holidays.

Then in the table measure use "IF" statements to control either using the Group Holiday column, or the country specific column, dependant on level of drill (can this be picked up?)

Has anyone else had this situation? How did you go about solving it or is their a "Classic" solution to this?

[marcus_sommer · Accepted solution]

I would create a calendar-dataset (concatenated) for each country (with Date + Country as Key) with all needed period-fields which includes also the workday-information of 1/0 as well as their continuous value per year/month/overall ... whatever is needed. And then a simple sum(WorkdayFlag) is enough.