Dynamic Calendars – Application

Quick reference

• 00:05 To build our Calendar Table, we actually need the three queries.
• 00:09 But unlike starting with new data source where we go to the Get Data button,
• 00:14 we're actually gonna do things a little bit differently here.
• 00:17 What we'd like to do is we'd like to base our starting date,
• 00:20 off of the first date in the sales table because that's probably where earliest
• 00:24 transactions are going to be.
• 00:26 I'm also going to pull the ending date from the last date in the budget's table
• 00:30 because typically budgets are uploaded before sales happen.
• 00:34 Now, if that doesn't work for
• 00:35 your organization, you need to adjust that logic.
• 00:38 But the idea here is that we want to base these calendar
• 00:41 pieces off of the tables that we're actually bringing in.
• 00:44 So to do that, I'm gonna go click on Edit Queries.
• 00:47 Now in the last example, we set up a staging table for budgets and sales.
• 00:51 And this is perfect because we can go and
• 00:53 grab the staging sales table, right click on it.
• 00:56 And we can say, reference.
• 00:58 That will create us a new query here which I am going to rename as Start Date.
• 01:03 Notice that I capitalized the S and the D.
• 01:06 It is not important except that whatever I do with this later on has to use the same
• 01:10 case.
• 01:11 I also do not want to load this particular piece to the model, so
• 01:15 I am going to uncheck the enable load button as well.
• 01:18 Now what am I gonna do with that?
• 01:20 Well here is the deal, everything here is around dates.
• 01:23 So I'm gonna right click on the date column and say remove other columns.
• 01:28 This gives me a column of dates.
• 01:30 There's about 38,000 here remember.
• 01:32 So now I'm gonna right click and I'm gonna say, you know what why don't you
• 01:36 remove the duplicates, and then why don't we filter it.
• 01:41 And say give me a date filter for
• 01:44 is the earliest date and this will bring me back the first date in the table.
• 01:49 But you'll notice that it's not January first.
• 01:51 So, instead of working with January second, which is the date format that I'm
• 01:55 showing on my system here, I am going to transform this column.
• 02:00 I'm going to transform it to a date for the year.
• 02:03 And I'm going to transform it to the start of year and
• 02:06 that will take it back to January first.
• 02:08 And I can now right click and drill down, and I've got right into
• 02:13 January first 2015 is the first date based on the sale tables, so that's perfect.
• 02:18 Now I need my end date, so I'm going to right click this one,
• 02:20 I'm going to grab from budgets and we're going to use basically the same process.
• 02:24 So, we're going to use right click and reference it.
• 02:27 This one I would change to be End Date, capital E, capital D, no spaces.
• 02:33 I'm also gonna right click and say to disable the load.
• 02:38 And now for the date table, right click, remove all the columns, right click,
• 02:43 remove duplicates.
• 02:45 And then I'm gonna filter but this time,
• 02:48 I'm gonna filter to say, is latest because I want the last date in this table.
• 02:53 And there we are, December 31st 2016 is the last budget that we uploaded.
• 02:58 Now in this case it looks like it's fine but
• 03:00 what if I only uploaded budgets to November 30th?
• 03:02 I wanna force this to be December 31st all the time.
• 03:06 So what I'm gonna do?
• 03:07 Again, let's go to transform.
• 03:09 You go to date, year and I'll transform it to end of year just in case I
• 03:14 don't get this exactly right next time around.
• 03:17 Or if the budgets don't extend to the entire end of year.
• 03:20 Now I can right click again and
• 03:22 say Drop Down and I now have a beautiful little Start Date and an End Date.
• 03:28 Now I want to turn them into a calendar.
• 03:31 So what I'm gonna do, is in the Queries pane on the left here,
• 03:34 I'm gonna right click and say New Query.
• 03:37 This one is gonna be from a Blank Query.
• 03:40 I'm going to give this query a nice little name, my personal favorite is calendar.
• 03:47 And what I'm going to do is in the formula bar again if you don't see this go up to
• 03:51 the view tab and check the formula bar.
• 03:53 We need to type it very specific formula and
• 03:55 that's going to be equals open curly bracket number, dot,
• 03:59 from, open parenthesis, this is going to be start date.
• 04:04 So however you type your start date, needs to be replaced exactly the same.
• 04:08 Close the parenthesis, dot, dot, number, dot, from,
• 04:12 open the parenthesis, end date, case the same as what you wrote before,
• 04:17 close your parenthesis, close your curly brackets.
• 04:20 Once you've done that, and you hit enter,
• 04:22 it will create you this list of date serial numbers.
• 04:25 Now, this formula here, if you use Start Date and
• 04:27 End Date spelled the way that I have, this formula here never ever changes.
• 04:31 The big key is here, you want to make sure you don't forget that step,
• 04:34 to drill down into this value.
• 04:35 Don't leave it at this table you must scroll down and
• 04:39 at that point you can create your calendar list that will self update.
• 04:42 Okay, great, but it doesn't look very nice.
• 04:45 You'll also notice it's not much we can do with it.
• 04:47 The Transform, Add Column tabs, they all grade out everything in here and
• 04:50 that's because this is a list which is a very special item.
• 04:53 We need to convert this to get our commands back to a table.
• 04:55 So we're going to click table and then as fast as you possibly can,
• 04:59 you're going to click the OK button because you always take the defaults
• 05:02 on that one when you're working with this particular piece.
• 05:04 So don't let it slow you down.
• 05:06 Now, I'm going to change the data type to a date.
• 05:11 And lo and behold, I have a calendar that will expand 731 rows from January 1,
• 05:15 2015 all the way to December 31, 2016.
• 05:17 I can now rename it to be Date.
• 05:22 And now I'm gonna add the fields I want.
• 05:24 I'm gonna select the date, go to Add Column, Date, Year, and choose a year.
• 05:31 And that will add me the year.
• 05:33 I am going to select the date column again.
• 05:35 Go back to Add Column.
• 05:37 Date, Month, Month.
• 05:39 This would give us the month number.
• 05:41 Select the Date column again.
• 05:42 Go back to Date, Month.
• 05:45 Why don't we get the name of month.
• 05:47 The name of month comes out really long.
• 05:48 So I am now going to change this.
• 05:50 I'm going to select the month name.
• 05:52 Go to Transform.
• 05:52 And I'm going to choose to extract the first three characters.
• 05:59 So enter, and that will shorten the date up there for me nicely.
• 06:03 Something else I might want to add back to selecting date, back to add column,
• 06:07 is date, month End of Month.
• 06:12 Most important thing to remember here is select the date column, and
• 06:15 choose to add column when you want to add a new one.
• 06:18 Don't choose transform, or it will convert this particular date.
• 06:21 The last thing I'm gonna do, set the data types.
• 06:23 So this guy here, I'm gonna set to text, because I don't like commas in my years.
• 06:28 The month I'll set to a whole number.
• 06:31 And the last couple, I think, we're probably good with those ones.
• 06:34 So at this point I can now say home, close and apply.
• 06:40 Take a look at my relationships, here's my calendar table.
• 06:44 I'm gonna link date to date, right click and hide.
• 06:49 Date to date, right click and hide.
• 06:54 And now when I come back over to the other side, grab a column chart.
• 06:59 You'll notice that I'll be able to take my calendar and
• 07:03 I can put end of month on the axis.
• 07:06 Now it gives me a hierarchy right away, I can change this to what I want,
• 07:09 which is End of Month.
• 07:11 And then I could go and throw my Sales.
• 07:14 Total sale, right on there.
• 07:15 I get a nice little chart that show the nice little sale cycle,
• 07:18 as well as my budget's amount.
• 07:20 And you will notice at this point,
• 07:21 I have got a chart that looks pretty good to show me my comparisons between the two.
• 07:26 So there we go.
• 07:27 The beautiful thing about this is this calendar will always update every time I
• 07:30 hit the refresh button.
• 07:31 To base it off of the last of the budget dates as
• 07:35 well as the beginning of the sales dates.

Lesson notes are only available for subscribers.