Livio/ July 15, 2018/ Excel, Power Query/ 11 comments

Creating a 4-4-5 Calendar with Power Query

4-4-5 calendar is a common calendar structure for some industries such as retail and manufacturing. You will need to create such a calendar if you wish to harness the full power of DAX measures in order to analyze data following this calendar structure. Power Query 4-4-5 calendar

You can download the excel file HERE

The M code I am sharing is one I have created which allows me to quickly and dynamically create such a calendar whenever I need it. It can be easily adapted to a 4-5-4 or 5-4-4 type of calendar. 

The only part that you will need to modify in order to adapt it to your analysis is the following (at the very end of the code):

CreateWholeCalendar(#date(2018,6,29),2019,5)

where:

  • #date(2018,6,29): is the first day of the first period of the first fiscal year you want to create the calendar for
  • 2019: is the first fiscal year
  • 5: is the number of fiscal years you want to create

Copy paste the below M code into your PQ advanced editor, then add it to your model and WOW your boss with some cool 4-4-5 analysis 🙂 and of course, let me know if this has helped you or if there are things you’d improve or add!

11 Comments

  1. This is great, i need to edit to a 5-4-4 calendar but my result is continually off. Do you happen to have the code for 5-4-4 version, or point me to the part of the code i need to adjust? Thanks!

    1. Hi Sarah – thanks for your feedback.

      The part to change would be the one below:

      Tbl28 = Table.AlternateRows(InitialDates,56,35,56),
      Tbl35 = Table.AlternateRows(InitialDates,0,56,35)

      Essentially for Tbl28 we are saying: take the first 56 (two times 4 weeks, 4-4) rows of the table InitialDates, then skip 35 rows (5 weeks, 5) and then keep the next 56 rows (two times 4 weeks). And repeat the last two steps for all the table.

      Tbl35, on the other hand, takes no amount of initial rows, skip the first 56 rows (two times four weeks, 4-4) and keeps the next 35 rows (5 weeks, 5). And repeat the last two steps for all the table.

      Therefore, in order to convert it into a 5-4-4 version what we would need to do is:
      Take the first 35 rows, skip the next 56 rows and keep the next 35 tows. Tbl35 becomes:

      Tbl35 = Table.AlternateRows(InitialDates,35,56,35)

      and Tbl28 becomes:

      Tbl28 = Table.AlternateRows(InitialDates,0,35,56),

      Please do test it on your side and let me know if it works!

  2. Hi brilliant bit of Code do you know how you can get the fiscal period for more than one year? my years run for 10 years for example

    1. Hi Craig,

      if I understand correctly, you would just need to update the very last line of the code, i.e.:
      CreateWholeCalendar(#date(2018,6,29),2019,5)

      where:

      #date(2018,6,29): is the first day of the first period of the first fiscal year you want to create the calendar for
      2019: is the first fiscal year
      5: is the number of fiscal years you want to create

    1. Thank you Joe. I am glad you found it helpful!

  3. Hi Livio,

    I just tried this out and it was great, but I ran into this problem
    (1) I used your code for 2017 to 2019. However, I noticed that the 2017 fiscal year ended on 12/30/2017 and the 2018 fiscal year started on 12/31/2017. In our company the 365th day of the year gets added to the last fiscal month. Is there a way to adjust the code to handle these end of year rounding issues. Our fiscal year is still 365 days (or 366 days in leap year).

    (2) Another idea or suggestion is to include offsets.
    I’m new to PowerQuery/PowerPivot, but I watched Avi Singh talk about offsets and think they sound like a great idea. His video is below…
    https://www.youtube.com/watch?v=XjVLaVLluYE&t=416s

    1. Hi Bill – thanks for the feedback!
      4-4-5 calendars are characterized by having 364 days for each year therefore it does not coincide with a normal calendar.
      If I understood correctly, you are looking to have the following calendar (which is not a pure 4-4-5):

      Quarter 1, 2 and 3:
      4 (weeks) – 28 days
      4 (weeks) – 28 days
      5 (weeks) – 35 days

      Quarter 4:
      4 (weeks) – 28 days
      4 (weeks) – 28 days
      5 (weeks) + 1 day (or 2 days if leap year) – 36 days

      Is this correct?

  4. This is awesome. Just started working for a company that uses 445, and this will be very helpful. One question – Our company uses the 445 throughout the year, but always ends the year on 12/31, regardless of how the days fall on the 445. Is there a way to incorporate that into this code?

    1. Hi David – see my response to Bill’s comment. Is your scenario the same?

      1. Yes! I guess it is!

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*