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 =
 VAR NYD =
     DATE ( Years[Year], 11 )
 VAR NYWD =
     WEEKDAY ( NYD2 )
 VAR NYDADD = 8 - NYWD
 RETURN
     IF ( NYWD > 5NYD + NYDADDNYD )

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 =
 VAR BH =
     DATE ( Years[Year], 1225 )
 VAR BHWD =
     WEEKDAY ( BH2 )
 VAR BHADJ = 8 - BHWD
 RETURN
     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 =
 VAR CD =
     DATE ( Dates[Date].[Year], 1225 )
 VAR CDWD =
     WEEKDAY ( CD2 )
 VAR CDADJ = 8 - CDWD
 VAR BH =
     IF ( CDWD > 5CD + CDADJCD )1
 VAR BHWD =
     WEEKDAY ( BH2 )
 VAR BHADJ = 8 - BHWD
 RETURN
     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.

VAR BHADJ = 8 - BHWD

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:

WordPress.com Logo

You are commenting using your WordPress.com 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