UK Bank Holidays in Power Query – Easter

In my previous post I showed how to calculate all of the UK Bank Holidays for a nominated year,all holidays except the Easter holidays. In this post we will wrap it up by showing how to calculate those holidays.

The holidays covered in the previous post were relatively straightforward, a matter of determining the day of the week for the holiday and adding or subtracting a number of days so as to avoid weekends or to force the date to a Monday.

Easter is a lot more complicated. I won’t go into the details of how to calculate it here, instead I will refer you to to this article in the Independent,  and more detail than you will ever need in this Wikipaedia article.

One of the days is easy, Easter Friday is the Friday before Easter Monday, Easter Moday is the Monday after Easter Friday, so we just need to calculate one, the other is easily derived by adding or subtracting 3 days. I decided to base my Power Query calculation on a formula that I have used in Excel for many years to calculate Easter Monday. This formula is

=ROUND(("4/"&target.year)/7+MOD(19*MOD(target.year)-7,30)*.14,0)*7-6+1

This works as Excel dates are stored as the number of days from 1st Jan 1900, today’s date (26th Feb 2016) has a day number of 42426. When you see a date, you are seeing that number formatted in your desired date format. The formula above determines a starting date of 1st April for the nominated year, and does some arithmetic on that underlying date number to get to the date number for Easter Monday. Unfortunately, as far as I am aware Power Query does not provide us with that capability, so we have to roll our own.

As an aside, I make no claims to this formula, it is something I picked up some time ago, because it works.

To facilitate this calculation, I have created three functions, one to calculate the number of days in a given year, another to calculate days to date within a given year. The final function, is a simple function that calls DaysInYear for every year required (1900 to year-1   of the target date in our case) and also calls DayNumberofYear for the year the target date.

DaysInYear Function

I have commented the function, so I hope that it is easy to follow. The approach is to generate a list of dates for the 1st day of each month in the nominated year. This list is then passed to the Power Query Date.DaysInMonth function to generate a list of the number of days in each month in that year, this list is then summed.

 (year) => 

    let

        last.day.year = Date.FromText("31/12/" & year),
        //setup a list of month numbers
        list.Months = List.Numbers(1,12),
        //create a list of first day of each month
        list.FirstOfMonths = List.Transform(  
                                 list.Months,
                                 each Text.From(year)&"-"&Text.From(_)&"-1"),
        //transform to dates
        list.FirstAsDates = List.Transform( 
                                list.FirstOfMonths,
                                each DateTime.Date(DateTime.FromText(_))),
        //create list of the days in each month 
        list.DaysInMonths = List.Transform(
                                list.FirstAsDates,
                                each Date.DaysInMonth(_))
    in     
        //sum the list of days in months                         
        List.Sum(_list.DaysInMonths)

DayNumberOfYear Function

This function is similar to the DaysInYear function in that it generates a list of days in each month for the year of the nominated date, but it only generates a list for the months before the month nominated date. These days are summed and added to the daynumber of the nominated month.

(date) =>

    let

        //transform parameter to true date
        target.date = DateTime.Date(DateTime.FromText(date)),
        //get year and month of target date
        year = Date.Year(target.date),
        month = Date.Month(target.date),
        //build a list of months prior to month of target date
        list.Months = List.Numbers(1, month-1),
        //generate a list for the first of each month
        list.FirstOfMonths = List.Transform(
                                 list.Months,
                                 each Text.From(year)&"-"&Text.From(_)&"-1"),
        list.FirstDates = List.Transform(
                              list.FirstOfMonths, 
                              each  DateTime.Date(DateTime.FromText(_))),
        list.DaysInMonths = List.Transform(
                                list.FirstDates,
                                each Date.DaysInMonth(_))
    in
        //sum the monthly day values in list and addin the day of this month
        if List.Sum(list.Days) = null then 0 else List.Sum(list.DaysInMonths)+Date.Day(target.date)

DaysToDate Function

Here we build a list of every year from 1900 until the year before the year of the date we are calculating the days to date for. Each year in this list is passed to our DaysInYear function to generate a list of days for each year. This is summed and added to the days to date for the date we are calculating.

If you notice, when  these values are summed I add a test to see whether our list of years includes 1900, adding an extra 1 if it does. This is because Excel erroneously treats 1900 as a leap year, and so will add 366 days for that year. Power Query does not treat 1900 as a leap year, so it calculates 365 days for that year. Although Power Query is handling this correctly, we are emulating that Excel formyula so we add 1 if we include 1900. I know that, as we emulate Excel as in this situation, our list of years will always include 1900, but I have built it so that it is a more generic function.

(target as date) =>

    let


        //setup the list of years to pass to DaysInYear function - 1900-target year-1
        list.Years = List.Numbers(1900, Date.Year(target)-1900),
        //genereate a list of days in each year
        list.DaysInYears = List.Transform(  
                               list.Years,
                                each DaysInYear(Text.From(_))),
        //calculate day number in year of target date
       days.YTD = Number.From(DayNumberOfYear(Date.ToText(DateTime.Date(target)))),
        //sum them both
        total.Days = List.Sum(list.DaysInYears) + days.YTD + (if List.Contains(list.Years, 1900) then 1 else 0)
    in
        total.Days</pre>

Easter Holidays

With these 3 functions, we can now emulate the Excel Easter Monday formula shown above.

Easter.Monday = Date.From(Number.Round(DaysToDate(#date(target.Year,4,1))/7+Number.Mod(19*Number.Mod(target.Year, 19)-7, 30)*.14, 0)*7-6+1),

It looks complex, but each part aligns nicely to the Excel formula. The main addition is that whereas the Excel date, “4/”&target.year, can be directly manipulated arithmetically, in Power Query we have to calculate the number of days, DaysToDate(#date(target.Year,4,1)).

The part that emulates the Excel formula, Number.Round(DaysToDate(#date(target.Year,4,1))/7+Number.Mod(19*Number.Mod(target.Year, 19)-7, 30)*.14, 0)*7-6+1, calculates the number of days from 01/01/1900, whichis passed to the Date.From function to get a date in the format we are familiar with.
As previously mentioned, if we know Easter Monday, Easter Friday is simply derived by subtracting 3 days

Easter.Friday = Date.AddDays(Easter.Monday, -3),

Final Holiday List Generator

The fulll routine that produces a list of the holidays is shown below. The target year is hard-coded here, it would be simple to parameterise it in some way.

let  
  
    target.year = 2015,

    Jan1 = #date(target.year,1,1),
    Jan1DoW = Date.DayOfWeek(Jan1, Day.Monday),
    New.Years.Day = if Jan1DoW>4 then Date.AddDays(Jan1,7-Jan1DoW) else Jan1,
    
    Easter.Monday = Date.From(Number.Round(DaysToDate(#date(target.year,4,1))/7+ Number.Mod(19*Number.Mod(target.year, 19)-7, 30)*.14, 0)*7-6+1),
    Easter.Friday = Date.AddDays(Easter.Monday, -3),
    
    May1 = #date(target.year,5,1),
    May1DoW = Date.DayOfWeek(May1,Day.Monday),
    May.Day = Date.AddDays(May1,Number.Mod(7-May1DoW,7)),

    May31 = #date(target.year,5,31),
    May31DoW = Date.DayOfWeek(May31,Day.Monday),
    Spring.Bank.Holiday = Date.AddDays(May31,-May31DoW),

    Aug31 = #date(target.year,8,31),
    Aug31DoW = Date.DayOfWeek(Aug31, Day.Monday),
    Summer.Bank.Holiday = Date.AddDays(Aug31, -Aug31DoW),

    Dec25 = #date(target.year,12,25),
    Dec25DoW = Date.DayOfWeek(Dec25, Day.Monday),
    Christmas.Day = if Dec25DoW>4 then Date.AddDays(Dec25,7-Dec25DoW) else Dec25,
    Boxing.Day = Date.AddDays(Christmas.Day, if Dec25DoW=4 then 3 else 1), 

    Holiday.List = Table.FromList(
                       {[A="New Year's Day", B=New.Years.Day],
                        [A="Easter Friday", B=Easter.Friday], 
                        [A="Easter Monday", B=Easter.Monday], 
                        [A="May Day", B=May.Day],
                        [A="Spring Bank Holiday", B=Spring.Bank.Holiday],
                        [A="Summer Bank Holiday", B=Summer.Bank.Holiday],
                        [A="Christmas Day", B=Christmas.Day],
                        [A="Boxing Day", B=Boxing.Day]},
                       Record.FieldValues, {"Holiday", Text.From(target.year)})
in
    Holiday.List

In Summary

There we have it, a list of all UK Bank Holidays for a given year. This also shows how we can leverage legacy Excel formulas within Power Query.

The Two functions DaysInYear and DayNumberOfYear are very similar in concept and in processing steps, so they could be combined into a single function, or DayNumberOfYear could call a modified version of DaysInYear, all in the cause of removing duplicate code..

Building a list of days in every year since 1900 does seem overkill, but although I scoured the Power Query specs, the Formula LanguageSpecification  and the Formula Library Specification, I cannot see any way of getting the number of days for a period within Power Query, hence my functions. In Power Query, dates have a date type, they are not just numbers with a special date display format.

One interesting thing that I found is in the Power Query Date.From function. As I show in the Easter Monday calculation, give Date.From a number and it will return a date. Passing the number 42426, the value that Excel stores for today’s date of 26th Feb 2016, it returns the date 26/02/2016. As Power Query correctly treats 1900 as not a leap year, I was expecting 25/02/2016, and if you count the days directly in Power Query, 26th Feb is indeed a day number of 42425. It seems that Power Query has picked up some of Excel’s legacy errors.

And finally, for those who cannot get enough Excel (isn’t that everyone?), Debra Dalgleish has a comprehensive summary of algorithms, Excel formulae, and UDFs that calculate Easter on her Contextures site.

 

Advertisements
This entry was posted in Excel, Functions, M Code, Power BI, Power Query, UK Bank Holidays and tagged . Bookmark the permalink.

4 Responses to UK Bank Holidays in Power Query – Easter

  1. Hi Bob,
    thanks a lot for this very useful series on holidays.

    Re counting the number of days in a period: There is a shorter way, which might be a bit counterintuitive:
    = List.Count({Number.From(#date(2016,01,01))..Number.From(#date(2016,12,31))})

    • wessexbob says:

      Thanks Imke, I was sure I was missing something, and I was, it was Number.From. I looked for a Date.ToNumber, Number.FromDate, but never thought of Number.From (which I should have as I used the similarly constructed Date.From).

      Having played with Number.From, and as I want the number from day 0, I realise I can do away with the list entirely, and just use Number.From on my target date, or
      Number.From(#date(target,year,4,1)
      instead of calling my functions as it aligns with Excel’s error as does Date.From.

      Now that is much nicer, and will be a whole load more efficient.

  2. RB says:

    Hi,

    I’m usure to what the -1t in the formula is doing? e.g. Text.From(_)&”-1t.FirstDates
    Thanks
    RB

  3. wessexbob says:

    I’m not sure where you are seeing that RB, there is nothing like that in the code that I can see. Many Text.From(_)&”-1″, but not with a t or FirstDates.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s