Creating a custom calendar in Power Query

As it’s the beginning of a new year, I thought it might be interesting to show my spin on creating a custom calendar in Power Query. This topic has been covered by many others, but I’ve never put my own signature on it.

Our goal

If you’re building calendar intelligence in Power Pivot for custom calendars, you pretty much need to use Rob Collie’s GFTIW pattern as shown below:

=CALCULATE([Measure],
ALL(Calendar445),
FILTER(
ALL(Calendar445),
Calendar445[PeriodID]=VALUES(Calendar445[PeriodID])-1
)
)

Note:  The pattern as written above assumes that your calendar table is called “Calendar445”.  If it isn’t, you’ll need to change that part.

This pattern is pretty robust, and, as shown above, will allow you to return the value of the measure for the prior period you provide.  But the big question here is how you create the needed columns to do that.  So this article will focus on building a calendar with the proper ID columns that you can use to create a 445, 454, 455 or 13 month/year calendar.  By doing so, we open up our ability to use Rob Collie’s GFITW pattern for a custom calendar intelligence in Power Pivot.

For more on this pattern see http://ppvt.pro/GFITW

A bit of background

If you’ve never used one of these calendars, the main concept is this:  Comparing this month vs last month doesn’t provide an apples to apples comparison for many businesses.  This is because months don’t have an consistent number of days.  In addition, comparing May 1 to May 1 is only good if your business isn’t influenced by the day of the week.  Picture retail for a second.  Wouldn’t it make more sense to compare Monday to Monday?  Or the first Tuesday of this month vs the first Tuesday of last month?  That’s hard to do with a standard 12 month calendar.

So this is the reason for the custom calendar.  It basically breaks up the year into chunks of weeks, with four usual variants:

  • 445:  These calendars have 4 quarters per year, 3 “months” per quarter, with 4 weeks, 4 weeks and 5 weeks respectively.
  • 454:  Similar to the 445, but works in a 4 week, 5 week, 4 week pattern.
  • 544:  Again, similar to 445, but works in a 5 week, 4 week, 4 week pattern
  • 13 periods: These calendars have 13 “months” per year, each made up of 4 weeks

The one commonality here is that, unlike a standard calendar, the custom calendar will always have 364 days per year (52 weeks x 7 days), meaning that their year end is different every year.

Creating a custom calendar

In order to work with Rob’s pattern, we need 5 columns:

  • A contiguous date column (to link to our Fact table)
  • YearID
  • QuarterID
  • MonthID
  • WeekID
  • DayID

With each of those, we can pretty much move backwards or forwards in time using the GFITW pattern.

Creating a custom calendar – Creating a contiguous date column

To create our contiguous date column, we have a few options.  We could follow the steps in this blog post on creating a dynamic calendar table.  Or we could skip the fnGetParameter function, and just directly query our parameter table.  Whichever method you choose, there is one REALLY important thing you need to do:

Your calendar start date must be the first date of (one of) your fiscal year(s).

It can be this year or last year, but you need to determine that.  I’m going to assume for this year that my year will start on Sunday, Jan 3, 2016, so I’ll set up a basic table in Excel to hold the dates for my calendar:

SNAGHTML25692

Notice the headers are “Parameter” and “Value”, and I also named this table “Parameters” via the Table Tools –> Design tab.  For reference, the Start Date is hard coded to Jan 3, 2016, and the End Date is a formula of B4+364*2 (running the calendar out two years plus a day.)

Now I’m ready to pull this into Power Query and build my contiguous list of dates.

  • Select any cell in the table –> Create a new query –> From Table
  • Remove the Changed Type step (as we don’t really need it)

This should leave you with a single step in your query (Source), and a look at your data table.

  • Click the fx button on the formula bar to add a new custom step

SNAGHTML78078[4]

This will create a new step that points to the previous step, showing =Source in the formula bar.  Let’s drill in to one of the values on the table.  Modify the formula to:

=Source[Value]{0}

image

Reading this, we’ve taken the Source step, drilled into the [Value] column, and extracted the value in position 0.  (Remembering that Power Query starts counting from 0.)

Now this is cool, but I’m going to want to use this in a list, and to get a range of values in a list, I need this as a number.  So let’s modify this again.

=Number.From(Source[Value]{0})

image

Great stuff, we’ve not got the date serial number for our start date. Let’s just rename this step of the query so we can recognize it.

  • Right click the Custom1 step –> Rename –> StartDate

Now, let’s get the end date.

  • Copy everything in the formula bar
  • Click the fx button to create a new step
  • Select everything in the formula bar –> paste the formula you copied
  • Update the formula as follows:

=Number.From(Source[Value]{1})

That should give you the date serial number for the End Date:

image

Let’s rename this step as well:

  • Right click the Custom1 step –> Rename –> EndDate

We’ve now got what we need to create our calendar:

  • Click the fx button to create a new step
  • Replace the text in the formula bar with this:

={StartDate..EndDate}

If you did this right, you’ve got a nice list of numbers (if you didn’t, check the spelling, as Power Query is case sensitive).  Let’s convert this list into something useable:

  • Go to List Tools –> Transform –> To Table –> OK
  • Right click Column1 –> Rename –> DateKey
  • Right click DateKey –> Change Type –> Date
  • Change the query name to Calendar445
  • Right click the Change Type step –> Rename –> DateKey

The result is a nice contiguous table of dates that runs from the first day of the fiscal year through the last date provided:

SNAGHTML16e36f

Creating a custom calendar – Adding the PeriodID columns

Now that we have a list of dates, we need to add our PeriodID columns which will allow the GFITW to function.

Creating a custom calendar – DayID column

This column is very useful when calculating other columns, but can also be used in the GFITW formula to navigate back and forward over days that overlap a year end.  To create it:

  • Go to Add Column –> Index –> From 1
  • Change the formula that shows up in the formula bar to:

=Table.AddIndexColumn(DateKey, "DayID", 1, 1)

  • Right click the Added Index step –> Rename –> DayID

NOTE:  The last two steps are optional.  Instead of changing the formula in the formula bar, you could right click and rename the Index column to DayID.  Personally, I like to have less steps in my window though, and by renaming those steps I can see exactly where each column was created when I’m reviewing it later.

What we have now is a number that starts at 1 and goes up for each row in the table.  If you scroll down the table, you’ll see that this value increases to 729 for the last row of the table.  (Day 1 + 364*2 = Day 729).

Creating a custom calendar – YearID column

Next, let’s create a field that will let us navigate over different years.  To do this, we will write a formula that targets the DayID column:

  • Go to Add Column –> Add Custom Column
    • Name:  YearID
    • Formula:  =Number.RoundDown(([DayID]-1)/364)+1
  • Right click the Added Custom step –> Rename –> YearID

If you scroll down the table, you’ll see that our first year shows a YearID of 1, and when we hit day 365 it changes:

image

The reason this works for us is this:  We can divide the DayID by 364 and round it down.  This gives us 0 for the first year values, hence the +1 at the end.  The challenge, however, is that this only works up to the last day of the year, since dividing 364 by 364 equals 1.  For that reason, we subtract 1 from the DayID column before dividing it by 364. The great thing here is that this is a pattern that we can exploit for some other fields…

Creating a custom calendar – QuarterID column

This formula is very similar to the YearID column:

  • Go to Add Column –> Add Custom Column
    • Name:  QuarterID
    • Formula:  =Number.RoundDown(([DayID]-1)/91)+1
  • Right click the Added Custom step –> Rename –> QuarterID

The result is a column that increased its value every 91 days:

image

It’s also worth noting here that this value does not reset at the year end, but rather keeps incrementing every 90 days.

Creating a custom calendar – MonthID column

The formula for this column is the tricky one, and depends on which version of the calendar you are using.  We’re still going to create a new custom column, and we’ll call it MonthID.  But you’ll need to pick the appropriate formula from this list based on the calendar you’re using:

Calendar Type Formula
445 Number.RoundDown([DayID]/91)*3+
( if Number.Mod([DayID],91)=0 then 0
else if Number.Mod([DayID],91)<=28 then 1
else if Number.Mod([DayID],91)<=56 then 2
else 3
)
454 Number.RoundDown([DayID]/91)*3+
( if Number.Mod([DayID],91)=0 then 0
else if Number.Mod([DayID],91)<=28 then 1
else if Number.Mod([DayID],91)<=63 then 2
else 3
)
544 Number.RoundDown([DayID]/91)*3+
( if Number.Mod([DayID],91)=0 then 0
else if Number.Mod([DayID],91)<=35 then 1
else if Number.Mod([DayID],91)<=63 then 2
else 3
)
13 periods Number.RoundDown(([DayID]-1)/28)+1

As I’m building a 445 calendar here, I’m going to go with the 445 pattern, which will correctly calculate an ever increasing month ID based on a pattern of 4 weeks, 4 weeks, then 5 weeks.  (Or 28 days + 28 days + 35 days.)

SNAGHTML5ac6c5

This formula is a bit tricky, and – like the GFITW pattern – you honestly don’t have to understand it to make use of it.  In this case this is especially true, as the formula above never changes.

If you’re interested however, the most important part to understand is what is happening in each of the Number.Mod functions.  That is the section that is influencing how many weeks are in each period.  The key values you see there:

  • 0:  Means that you hit the last day of the quarter
  • 28:  This is 4 weeks x 7 days
  • 35:  This is 5 weeks x 7 days
  • 56:  This is 8 weeks x 7 days
  • 63:  This is 9 weeks x 7 days

The Number.RoundDown portion divides the number of days in the DayID column by 91, then rounds down.  That will return results of 0 through 3 for any given value.  We then multiply that number by 3 in order to return values of 0, 3, 6, 9 (which turns out to be the month of the end of the prior quarter.)

The final piece of this equation is to add the appropriate value to the previous step in order to get it in the right quarter.  For this we look at the Mod (remainder) of days after removing all multiples of 91.  In the case of the 445, if the value is <= 28 that means we’re in the first 4 weeks, so we add one.  If it’s >28 but <=56, that means it’s in the second 4 weeks, so we add two.  We can assume that anything else should add 3… except if there was no remainder.  In that case we don’t add anything as it’s already correct.

Creating a custom calendar – WeekID column

WeekID is fortunately much easier, returning to the same pattern we used for the YearID column:

  • Go to Add Column –> Add Custom Column
    • Name:  WeekID
    • Formula:  =Number.RoundDown(([DayID]-1)/7)+1
  • Right click the Added Custom step –> Rename –> WeekID

The result is a column that increases its value every 7 days:

SNAGHTML4fc488

Finalizing the custom calendar

The last thing we should do before we load our calendar is define our data types.  Even though they all look like numbers here, the reality is that many are actually defined as the “any” data type.  This is frustrating, as you’d think a Number.Mod function would return a number and not need subsequent conversion.

  • Right click the DateKey column –> Change Type –> Date
  • Right click each ID column –> Change Type –> Decimal Number
  • Go to Home –> Close & Load To…
    • Choose Only Create Connection
    • Check Add to Data Model
    • Click OK

And after a quick sort in the data model, you can see that the numbers have continued to grow right through the last date:

image

Final Thoughts

We now have everything we need in order to use the GFITW pattern and get custom calendar intelligence from Power Pivot.  Simply update the PeriodID with the period you wish to use.  For example, if we had a Sales$ measure defined, we can get last month’s sales using the following:

=CALCULATE([Sales$],
ALL(Calendar445),
FILTER(
ALL(Calendar445),
Calendar445[MonthID]=VALUES(Calendar445[MonthID])-1
)
)

As an added bonus, as we’re using Power Query, the calendar will update every time we refresh the data in the workbook.  We never really have to worry about updating it, as we can use a dynamic formula to drive the start and end dates of the calendar.

As you can see from reading the post, the tricky part is really about grabbing the right formula for the MonthID.  The rest are simple and consistent, it’s just that one that gets a bit wonky, as the number of weeks can change.  (To be fair, this would be a problem for the quarter in a 13 period calendar as well… one of those quarter will need 4 weeks where the rest will need 3.)

One thing we don’t have here is any fields to use as Dimensions (Row or Column labels, Filters, or for Slicers.)  The reason I elected not to include those here is that the post is already very long, and they’re not necessary to the mechanics of the GFITW formula.

If you’d like a copy of the completed calendar, you can download it here.  Be warned though, that I created this in Excel 2016.  It should work nicely with Excel 2013 and higher, but you may have to rebuilt it in a new workbook if you’re using Excel 2010 due to the version difference on the Power Pivot Data Model.

21 thoughts on “Creating a custom calendar in Power Query

  1. Pingback: Excel Roundup 20160111 « Contextures Blog

  2. Ken,
    Your post is just something I need. Thanks a lot.
    But I still have the problem you mention at the end of your post.
    In the powerpivot table I have a week readable for humans (201601 etc.) but I'm forced to use week sequential number to calculate 4 weeks moving average. Only sequential number works on turn of the years. How could I reference week seq no not having it in pivot table? Any suggestion will be appreciated.
    Regards
    Adam

  3. Hi Ken,

    Thanks a lot! I'd like to say that putting your signature on this, made creating the custom 445 or like calendar the easiest way I've seen already. Awesome!

  4. Hey Adam.

    The columns given in the post provide a sequential numbering system, and don't reset at the end of any quarter or year, so I think they should already be doing what you need, and your issue is more around the DAX. I would think you should be able to use something like this, although I haven't tested this at all, and wrote it entirely in a browser:

    =CALCULATE(
    [Measure],
    ALL(Calendar),
    FILTER(
    ALL(Calendar),
    Calendar([WeekID])>=VALUES(Calendar[WeekID])-4,
    Calendar([WeekID])<=VALUES(Calendar[WeekID])-1 ) ) The [Measure] should then be AVERAGE(Sales[Amount]) or something (whatever it is you need.) My suggestion would be to pick up Rob Collie's DAX Formulas for Power Pivot or Matt Allington's Learn to Write DAX books to help get the finer points. You'll find them both to be super valuable resources. Hope that helps,

  5. What if i need to lookup the start and end date on a column found on a separate table.

    StartDate = Min(ColumnFromOtherTable)
    EndDate = Min(ColumnFromOtherTable)

  6. Hi Ken,
    Wondering if you have a solve for building a calendar to match the National Retail Federations 454. Every 4-5 years or so they have a 53 week year. thoughts

  7. Hello Ken, this post most likely saved my JOB, because it is so incredibly hard to find a step-by-step tutorial of how to build these custom calendars. The only piece that I still really need is the section of your tutorial for adding the PeriodID column. That part is blank for some reason on this post. Is there anyway you could add that back in? Thank you so much!

  8. Hi Rachel,

    There was some odd spacing in the article (which I've tried to clean up), but the content all still shows up for me. Can you skim it again, as it definitely does talk about how to create all these patterns.

  9. Thank you so much Ken. I was looking around for this and can't wait to incorporate it into my models.
    However the issue brought up above is a big obstacle: 454 calendar year 2017 has a 53rd week in January. This occurs every 5-6 (leap) years (2006, 2012, 2017 and 2023 are all 53-week years). How would you incorporate this into the logic?

  10. Hi Mickael, I'm not sure I have an answer on this one, except that I'd be looking at trying to define a more complex rule around the logic. (But every 5 or 6 doesn't work... it needs a defined rule.)

    I'll be completely honest that I've never understood the point of these 53 week versions. The whole point of a 445 is to compare the period against the same period last year, and if you add another week "sometimes" it throws that logic off.

  11. Ken,

    Maybe the answer to my question about adding the 53rd week to the calendar is to create another query with just that 53rd week and append it to the master query.

    Thanks for your enriching posts.

  12. Hey Mickael,

    It's an interesting thought, but I don't think it would work. If the calendar always resets at 52 weeks, you're going to introduced duplicate dates. For Power Pivot that would kill the ability to use it as a calendar table.

  13. Ken - nice post....

    In DAX, several of the Time Intelligence formulas depend on a full year's data in your calendar table. If you start the calendar on 1/3/16 (Skipping the 1st and 2nd) doesn't this break that rule? If you introduce the full year's days into the calendar table, you would end up with dates from 1/1/2016 to 12/31/2017. My question then, is how to identify the 1st week of the year? Our week is always Mon - Sun. So in our case, we would need to find the first Sunday of the year, (1/3/2016 in your example) and subtract 7 days days from it to get the first Monday of the full week (12/28/15 in this case). The DayID index then would need to start on that day, rather than a simple "add index column".

  14. One other comment.... if you are using the 13 month calendar, the quarters formula

    = Table.AddColumn(AddField_YearIndex, "QuarterIndex", each Number.RoundDown(([DateIndex]-1)/91)+1, Int64.Type)

    will not work as this assumes even 12 / 4 or 3 month quarters. When you have 13 months, one of your quarters will need to have 4, 28-day months, and the other 3 quarters will have three 28-day months, giving you 13 total months. You will need to resort to the "Mod" function much like the MonthID in 445, 454, or 544 calendars above.

  15. Hi Ken,

    As you say in one of your posts, the columns given in the post provide a sequential numbering system, and don't reset at the end of any quarter or year. Is it possible to restart every 52 weeks with a M-code?
    Eline

  16. Hi John,

    WRT DAX, those formulae require a contiguous list of dates, not necessarily dates that begin at the start of a calendar period. If you're using a 445 or 13 week variant, you can't use the regular YTD functions anyway, as those are based on Calendar years, not 364 day years. So you have to roll your own with DATESBETWEEN().

    (And sorry for the VERY late reply!)

  17. Eline, providing you have the columns I created above, you can create periods that reset. For Weeks, you'd select the Weeks column -> Add Column -> Standard -> Modulo -> 52. That should create a list of periods from 0-51 for each year.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.