Public holidays are hugely important when doing any time based analysis. For some businesses it might be a period where they close, for other businesses it could be peak trading.
When entering public holidays into Power BI you could just load in a table of dates from Excel. However this will require more maintenance. The ideal way to deal with public holidays is to have Power BI calculate them for you; then you can just leave the report to follow it’s refresh schedule.
These formulas will work just off the year so a good place to start would be getting a fresh table with just a column containing years. I’ll then add on calculated columns for each public holiday. This isn’t going to be how the table will end up looking, but it is a nice way of building up each formula – and checking they work – before making the final table in a later part.
I have a date table in my report that I created using CALENDARAUTO. I’ll just start a new table that pulls off the unique years. This will be a DAX table rather than one made in the query module so you go to Modelling -> New Table in the ribbon to create this and use the below formula.
Years = VALUES ( Dates[Date].[Year] )
I’m calculating English public holidays (known as bank holidays here) but you can probably use these patterns to work out public holidays in your own region. These don’t even need to be public holidays but just days of significance like Black Friday.
The dates I’m going to be calculating are:
- New Year’s Day
- Good Friday
- Easter Monday
- May Day
- Christmas Day
- Boxing Day
There are a few challenges to workaround here. New Year’s, Christmas and Boxing day are fixed dates on the calendar so easy right? But if that day falls on a weekend then the public holiday will be the next available working day.
May Day is the first Monday of May so no fixed date. Spring and Summer holidays fall on the last Monday of May and August respectively – again no fixed date.
Easter is something to do with full moons and equinoxes so is all over the place.
This gives us four patterns we need to use to calculate all of these public holidays:
- Fixed calendar date with an adjustment to next working day if needed
- First Monday of the month
- Last Monday of the month
- Dates relative to Easter Sunday
Lets start with Easter. Even though it is the most complicated to work out, some very clever people have already done the hard work for us. Check out the Computus article on Wikipedia if you want more info. There are a couple formulas in Excel that exploit how Excel handles dates to work this out. I’ve modified one of these very slightly to work in DAX.
Easter Day = ROUND ( DATE ( Years[Year], 4, 1 ) / 7 + MOD ( 19 * MOD ( Years[Year], 19 ) - 7, 30 ) * 0.14, 0 ) * 7 - 6
Good Friday is obviously 2 days before Easter Day so just change the – 6 at the end to – 8
Good Friday = ROUND ( DATE ( Years[Year], 4, 1 ) / 7 + MOD ( 19 * MOD ( Years[Year], 19 ) - 7, 30 ) * 0.14, 0 ) * 7 - 8
Easter Monday is one day after Easter Sunday so change the – 6 to – 5
Easter Monday = ROUND ( DATE ( Years[Year], 4, 1 ) / 7 + MOD ( 19 * MOD ( Years[Year], 19 ) - 7, 30 ) * 0.14, 0 ) * 7 - 5
Create these as calculated columns on the table we set up at the beginning and we now have this:
In the next part we’ll be looking at a pattern for next working day.