I have a Time sheet Table and a date dimension table and I am trying to see how many hours were worked on holidays this year by country. The issue I am running into is different countries and even within the US some states have different holidays.
The time sheet table has a column for "site" that has country-state-city that I can separate out when importing the data. I am unsure how to set up the date table to say july 4th is only a holiday in the US for example.
I am using MS Power BI and any help would be appreciated
You will need to set up another table for holidays with columns for date, country-state-city, holiday name. Add another column to combine date and country-state-city into a unique key column. Add a column for "IsHoliday" and set it to "yes" for all rows.
In the timesheet table you can then create the same key column of date and country-state-city and merge the holidays table, keeping all rows of the timesheet and adding the matches from the holidays. Expand the table and select only the "IsHoliday" column from the holidays table.
Now each timesheet entry has a flag that you can use in a measure or as a filter.