Locked lesson.
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
Download this lesson’s related exercise files.
Creating Dynamic Calendars - Theory.docx66.7 KB Creating Dynamic Calendars - Theory - Solution.docx
66.8 KB
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
- 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.