Locked lesson.

Upgrade

About this lesson

Every model that will perform any kind of date/calendar intelligence must have a proper calendar table.  This lesson gives you the tips that you can apply to any model to build a calendar table that dynamically updates with your data.

Exercise files

There are no related exercise files for this lesson.

Quick reference

Dynamic Calendars – Theory

How to create a dynamic self-updating calendar for your model.

When to use

When you need to build a calendar table on the fly that covers your entire date range.

Instructions

Create a StartDate query

  • Go to Edit Queries and locate the table that will have the earliest possible date
  • Right click the table and choose Reference
  • Rename your Query to StartDate
  • Right click the date column -> Remove other columns
  • Right click the date column -> Remove Duplicates
  • If the column is not formatted as a date, change the data type to a Date type
  • Filter the date column -> Is Earliest
  • Select the date column -> Transform -> Year -> StartOfYear
  • Right click the date -> Drill Down
  • On the left side, right click the new query and clear the Enable Load checkbox

Create an EndDate query using the same steps as above except:

  • Base it on the table that will have the latest possible date
  • Filter the date column to Is Latest instead of Is Earliest
  • Transform the Year to EndOfYear instead of StartOfYear

Create the Calendar query

  • Create a new Blank Query
  • In the formula bar enter the following formula: ={Number.From(StartDate)..Number.From(EndDate)}
  • Go to List Tools -> Transform -> ToTable
  • Click OK with the default options
  • Change the data type of Column1 to Date
  • Rename Column1 to Date
  • Add other date columns as desired
  • Load the query and link it in to your model

Your base calendar is now complete.  To add new columns to the calendar:

  • Select the Date column
  • Go to Add Column -> Date
  • Choose the Year, Month, Quarter, Week or Day format you’d like to add
  • Repeat from Step1 for each column you need to add

Hints & tips

  • If you are going to perform any data analysis, you should ALWAYS have a proper Calendar table in your model
  • Failure to have a good calendar can lead to misstated results due to gaps in your “fact” tables
  • The StartDate and EndDate do not need to come from the same base query
  • A good candidate for the EndDate is a Budget table, as budgets are typically prepared before the actual transactions occur

 

Login to download
  • 00:05 One of the things that's really important to have in any model where you're going
  • 00:09 to do a calendar style intelligence.
  • 00:11 Like trying to figure out month to date or
  • 00:14 year to date style metrics is you must have a specific dedicated calendar table.
  • 00:19 This is the one of the number one problems I see people try and
  • 00:21 avoid is to say, I'll just base it on my transactions table.
  • 00:25 That's a really bad idea.
  • 00:27 And the reason for that is because later on when you try writing some formulas
  • 00:31 against that particular calendar.
  • 00:32 If there is a gap, maybe because you're closed or there was a power outage or
  • 00:36 something like that, it will actually break your formula.
  • 00:39 So you don't want to avoid this step if you plan to do any calendar intelligence.
  • 00:43 Now, creating calendars is actually a very straightforward process once you
  • 00:48 understand how to do it.
  • 00:49 The first thing that we do is we create specific queries to drill into our
  • 00:53 StartDate and EndDate, and I'm gonna show you how to do that in the next module.
  • 00:58 I recommend that you always have your calendar span entire years,
  • 01:01 don't trim it to just show most recent dates.
  • 01:04 This works a lot better if you are trying to analyze things like budgets,
  • 01:07 or you're trying to compare this year versus last year.
  • 01:09 If you've got a calendar that actually spans that entire period of time.
  • 01:14 One you have your StartDate and
  • 01:16 EndDate queries, the next thing that we do is we actually go through the process
  • 01:20 of writing a little bit of custom code in order to make this work.
  • 01:22 And it's really short, it just looks a little bit weird.
  • 01:25 We start by creating a blank query, and
  • 01:28 then in the formula bar, we're gonna go and enter this funky looking formula.
  • 01:34 Now one of the things that's really important about writing formulas inside
  • 01:38 PowerQuery or inside PowerBI as well,
  • 01:41 is that we need to make sure that the brackets are correct.
  • 01:45 So some of them are curly, some of them are round.
  • 01:48 We need to make sure that the casing is correct where you see a capital N and
  • 01:51 number it must be a capital N.
  • 01:54 If you try and type this entire thing in upper case it will not work.
  • 01:57 If you try and type it all in lower case it will not work.
  • 02:00 Everything needs to be exactly as you see here if you want this step to work.
  • 02:06 What it does is it then creates a list of date serial numbers.
  • 02:10 So that's the number of days since January 1st, 1900, is what you're going
  • 02:14 to end up seeing, a great big list of those, you think, well how's that helpful.
  • 02:18 Well, when we convert the list into a table we can then change the data
  • 02:23 type on column one into a date and boom we've got a beautiful
  • 02:26 calendar that runs from whatever your StartDate is all the way to your EndDate.
  • 02:30 And it's dynamic if you set it up properly,
  • 02:33 which will always manage to be refreshed with your data so
  • 02:36 that it covers the date range you're looking for, which is just awesome.
  • 02:40 The next step is that we rename Column1 to say Date, cuz that just makes sense,
  • 02:44 then the table actually has a name that you can follow.
  • 02:47 And then we can use all of the in-built power query functions inside PowerBI to go
  • 02:51 and add other different columns as desired.
  • 02:54 Things like year or month, or month and
  • 02:56 name, or the end of month, those are some of the key columns that I generally use.
  • 03:00 But you'll notice there is a whole bunch of different varieties if these things
  • 03:03 available to you.
  • 03:04 So this is what we are going to do in the next video, is we're gonna go through and
  • 03:07 actually apply this to our sample model.
  • 03:09 To show you exactly how to build this dynamic calendar on the fly that self
  • 03:13 updates every time you refresh your data.

Lesson notes are only available for subscribers.

Gift this course