The Ultimate Date Table–Revisited

Guest post by Dominik Petri, who I am stoked to say is translating the book into German!  Today he shares a cool “hybrid” technique that blends two of my favorites things – Data Market and Power Query!

Take it away, Dominik…

As you might already know from Rob’s “The Ultimate Date Table” post, you need a separate calendar table to unleash the power of Power Pivot’s time intelligence functions. You want one for free? Fully customizable? Running up to a variable end date? Updated automatically every time you open your workbook? The time has come…

Get a great calendar table – for free

Boyan Penev has put together various calendar tables that you can download from Azure DataMarket directly into Power Pivot – for free. Again, see Rob’s postfor details. So far, so good. But it is a pain to filter the data range you want to import. But there is Power Query!

Filter and shape the calendar with Power Query

Instead of importing the calendar into the Power Pivot window, I use Power Query (the tool formerly known as Data Explorer). Power Query is part of Microsoft’s Power BI family. You can download the free add-in from here.

By using Power Query, you can easily filter the date range. Here is how:

Connect to DateStream with Power Query

In Excel, switch to the POWER QUERY tab and click From Other Sources – From Windows Azure Marketplace. In the Navigator pane on the right side of your screen, click DateStream. If you don’t see DateStream, you are not subscribed to DateStream in Windows Azure Marketplace. Check Rob’s initial blog post for how to subscribe.

The choice is yours: Select from eleven different calendars

Currently, there are 11 tables to choose from. Simply hover the mouse over a calendar an you’ll see a preview:

Filter Down to Relevant Dates

I go for the BasicCalendarUS. A double click on the calendar name opens the Query Editor. The calendar starts on January 1, 1900! Way too much data! The good news: You can easily narrow the date range by clicking the down arrow in the DateKey column and select Date/Time Filters > Between

Now I enter the start and end date in the Filter Rows dialog box:

After a few seconds, the preview is updated. If you don’t want to import every column, just right click on the column’s header and choose Remove from the context menu.

Import the calendar directly into Power Pivot

Since I want the calendar in Power Pivot and not on a worksheet, I check Load to Data Model in the Query Settings task pane and uncheck Load to worksheet. The query’s name will become the table’s name in the Power Pivot window. Feel free to change the name…

I am done (at least for now) and click Apply & Close on the Home tab:

Back in the Power Pivot window I see the new table called BasicCalendarUS with 5,479 days/rows.

Making the End Date dynamic

So far, I have hard wired the end date into the query. Not my style. Yours? I’d rather have the calendar end today. Or at the end of the current week. Or current month. So let’s make it dynamic!

Power Query comes with its own language called “M”. I can already see the eyes of the James Bond fans lighten up! Let’s get back to the query: Return to Excel and double click the query in the Workbook Queries task pane:

Like in Excel, there is a formula bar above the grid. The formula contains two #datetime functions that represent the dates you entered in the Filter Row dialog box:

Since I want the calendar to end today, I need to replace the second #datetime function. In Excel I would use TODAY(). The corresponding function in the Power Query formula language is called DateTime.LocalNow(). Be careful: The function names are case-sensitive!

Let’s say the data I want to analyze is lagging by 2 days. Therefore, I want the calendar to end two days before today. In Excel, I would use TODAY()-2. In “M” I need to subtract a duration value:

More variations for a dynamic End Date

It is also possible to have the calendar run up to the current week’s end. Here’s how:

Notice how I changed the operator from “is equal or smaller than” to “is smaller than”? I don’t know why, but Date.EndOfWeek(DateTime.Local.Now()) returns the first day of next week. It should return the last day of the current week with a timestamp of 23:59:59.9999 I guess it gets rounded up. I “cure” this by using the “is smaller than” operator

If you prefer the calendar to end at the end of the month or at the end of the year, use Date.EndOfMonth or Date.EndOfYear instead.

The icing on the cake: Automatically updated calendar

With a dynamic end date comes the need to update the calendar. Very convenient if this is done automatically every time I open the workbook. Back in the Excel window I open the Connection Properties dialog (DATA – Connections – Connections) and check Refresh data when opening the file.