Public holidays in Power BI part 3 – Calculating the first and last Monday

In the previous parts of this series on public holidays we looked at calculating when Easter falls in the year and how to find the next Monday. Now we’ll be trying to find the first and last Monday of a month.

The May Day public holiday falls on the first Monday of May in England. This just requires a slight change to the previous pattern to find the next Monday.

May Day =
VAR PH =
    DATE ( Years[Year], 51 )
RETURN
    VAR PHWD =
        WEEKDAY ( PH2 )
    VAR PHADJ = 8 - PHWD
    RETURN
        IF ( PHWD <> 1PH + PHADJPH )

I’m doing the same thing here, finding the adjustment required to calculate the next Monday. The only difference is in my IF statement; before it would only trigger if the date fell on a Saturday or Sunday, now it triggers if the weekday is not a Monday.

Now for the last Monday of the month pattern. In England we have the Spring public holiday which falls on the last Monday of May (this weekend! woo!) and the Summer public holiday is the last Monday of August.

Again this is a very similar pattern:

Spring =
VAR PH =
    DATE ( Years[Year], 531 )
RETURN
    VAR PHWD =
        WEEKDAY ( PH2 )
    VAR PHADJ = 1 - PHWD
    RETURN
        IF ( PHWD <> 1PH + PHADJPH )

For my date I’m taking the last day of May instead and my adjustment calculation becomes 1 – weekday number instead of 8 – weekday number. If this day falls on a Monday then it will be 1 – 1 so no adjustment. For any other day it will produce a negative adjustment so we will be going backwards from the reference date rather than forwards.

Like the next Monday pattern, these can be modified to find the first/last Tuesday, Wednesday, etc. by adding 1 to the adjustment for each day after Monday.

For the final part we’ll be looking at how to put these calculations into a more useable format.

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