Public holidays in Power BI part 2 – Calculating the next Monday

In part 1 we looked at calculating Easter Day. This time we’ll be looking at New Year’s, Christmas and Boxing day. If these fall on a weekend the public holiday will be the next available working day so we need a way to calculate the next working day.

This isn’t a true next working day calculation – part of the reason of calculating these public holidays is to allow for a true next working day calculation. This pattern is actually for finding the next Monday after the specified date.

I’ve created a simple pattern that can be used to find the next Monday which avoids nesting lots of IF statements.

New Years =
     DATE ( Years[Year], 11 )
     WEEKDAY ( NYD2 )

First I’ve created a variable called NYD that creates the 1st of January for that particular year. Then another variable, NYWD, that works out the weekday number (using UK method of Monday = 1). The final variable works out the number of days you need to add to the date to hit the following Monday.

Then we put that all together with an IF statement. If the weekday number is 6 or 7 (Saturday or Sunday) then add on the extra days required to hit the following Monday, otherwise just use the 1st of January.

Chrismas Day follows the same pattern. The first variable just changes to Christmas Day.

Xmas Day =
     DATE ( Years[Year], 1225 )
     WEEKDAY ( BH2 )
     IF ( BHWD > 5BH + BHADJBH )

Boxing Day needs a bit of tweaking to the pattern. If you just enter in the 26th of December into this pattern you can end up with a situation where it puts Boxing Day on the same day as Christmas Day (when Christmas Day is on a Saturday and Boxing Day is on a Sunday).

Boxing Day =
     DATE ( Dates[Date].[Year], 1225 )
     WEEKDAY ( CD2 )
     IF ( CDWD > 5CD + CDADJCD )1
     WEEKDAY ( BH2 )
     IF ( BHWD > 5BH + BHADJBH )

Essentially I’m nesting the pattern into the pattern. Boxing Day needs to be considered as Christmas Day + 1 rather than 26th of December. The first instance of the pattern is calculating the correct public holiday for Christmas Day and adding 1 to it. This date is then run through the pattern again.

Now the updated table:

You can modify this pattern to find the next Tuesday, Wednesday, etc. by adding 1 to the adjustment for each day after Monday. To find the next available Tuesday it would be a 9, for Wednesday a 10 and so on.


In part 3 we will be looking at the final two patterns – first Monday and last Monday of a month.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s