Creating Dynamic Calendars - Theory

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.

Quick reference

Creating Dynamic Calendars - Theory

An overview of creating 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 data range.

Instructions

The general process is:

Create StartDate and EndDate queries

  • Create a StartDate query that drills down to the earliest possible date in your data
  • Create an EndDate query that drills down to the latest possible date in your data

Create the Calendar Table

  • Create a new blank query
  • In the formula bar enter the following formula:

={Number.From(StartDate)..Number.From(EndDate)}

  • Convert your list to a table
  • Change the column’s Data Type to Date
  • Rename the column to Date
  • Add any other desired columns (e.g. year, month, end of month)

Hints & tips

  • If you are going to perform any data analysis, you should ALWAYS have a proper calendar table in your model that spans the entire fiscal year
  • Failure to have a good calendar can lead to misstated results due to gaps in your Fact tables
Login to download
  • 00:04 Our model for the Mulligan's Golf Course so far is missing one key table.
  • 00:10 It's a bridge table to be able to actually slice the transactions and
  • 00:15 the budgets by dates.
  • 00:17 Right now there's multiple dates in both of those tables, so
  • 00:20 we can't relate them together.
  • 00:22 But by building a specific bridge tabel for a calendar, we can.
  • 00:26 Now my favorite thing to do when I'm building these models
  • 00:29 is to actually build these calendars on a dynamic basis.
  • 00:32 What that means is I want to look at the data and try and
  • 00:35 figure out what is the earliest date that's ever gonna occur.
  • 00:38 And I want to programmatically pick that out of the actual data itself.
  • 00:42 The same for the end date query or end date.
  • 00:45 I also want to pick that out from the data as well.
  • 00:48 If I can do that, I can create two queries, one for start date and
  • 00:51 one for end date, that will dynamically update every time the data changes.
  • 00:55 So I'll never ever miss the beginning or end,
  • 00:58 and have a calendar that's missing days that are important.
  • 01:02 I highly recommend when you're building your calendar tables that you always span
  • 01:06 entire fiscal years.
  • 01:07 Now this means don't trim it, don't turn around say, hey,
  • 01:11 my data only runs till February 28, so I'll just stop right there.
  • 01:14 Unless that happens to be your year end.
  • 01:17 So what we do, is we look at it and say, well, if my data is going to start on
  • 01:21 January 3rd, I'll try and back it up so that my calendar starts on January 1st.
  • 01:27 And I'll run my end date so that it goes to December 31st.
  • 01:29 I don't want any missing dates in here.
  • 01:32 I can create queries that specifically do that.
  • 01:34 I can create,
  • 01:35 even better, Excel formulas because I've got a really good control of those.
  • 01:39 So, that I can actually generate my start and
  • 01:41 end date from the information that I actually need.
  • 01:45 What we do once we have these two queries, StartDate and
  • 01:48 EndDate, is we're gonna create a new blank query.
  • 01:52 And, then in the formula bar, we go in and we type in a very specific formula.
  • 01:56 That formula is Equals curly bracket, number dot from open parenthesis,
  • 02:01 start date, close parenthesis, dot, dot, number dot from, open parenthesis.
  • 02:07 End date close parenthesis and close the curly brackets.
  • 02:11 This will create what's called a list in power query.
  • 02:15 And it will be a list of consecutive numbers that runs from the start dates,
  • 02:20 date serial number, to the end dates, date serial number.
  • 02:24 Date serial number, what is that?
  • 02:26 Well it's the number of days since January first, 1900 to the specific date.
  • 02:34 Once we have that, you might be thinking, why would I want that?
  • 02:38 What we do is we actually take this list in Power Query and
  • 02:41 we convert it into a table.
  • 02:44 And then we set the column of that table to be a date data type.
  • 02:50 And that will convert all of those dates serial numbers into real dates.
  • 02:55 So now you've got a consecutive list of days from the start date all the way
  • 02:59 through to the end date that you can now link into your model.
  • 03:02 Which is perfect.
  • 03:04 And even better than that, once we rename that column one to be date,
  • 03:09 our calender tables actually done.
  • 03:11 Except, we might wanna go back and add more columns.
  • 03:15 Other things, other ways of slicing dates, maybe things like, I don't know,
  • 03:19 year, or, month.
  • 03:20 Well you can add as many of those columns as you desire, all you need to
  • 03:24 do is select the date column, go to the add column dialog in Power Query.
  • 03:28 Go to date and there's a whole bunch of preformat dates that you just
  • 03:31 need to click and choose which ones you want.
  • 03:33 It's pretty awesome.
  • 03:35 Let's go take a look in our next example here and
  • 03:37 see exactly how we can apply this into our model.

Lesson notes are only available for subscribers.

Gift this course