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…

Selecting a DateStream calendar from Power Query Navigator taskpane

 

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 SourcesFrom 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:

Selecting a DateStream calendar from Power Query Navigator taskpane

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

Using the Date Time Filter in the Power Query Editor

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

Select the start and end date for the date filter in the Power Query Editor

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…

Settings in Power Query Task Pane

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

Click Apply and Close in the Power Query 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:

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:

DateTime function in the Power Query Formula Bar

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!

Replace the DateTime function with DateTime.LocalNow

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:

Use the Duration function to add or subtract days from DateTime.LocalNow

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:

Changing the operator in the Formula Bar

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 Smiley

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.

Have the query updated automatically every time the workbook is opened

31 Responses to The Ultimate Date Table–Revisited

  1. Marco Russo says:

    Be careful importing the calendar dinamically. You should always import dates until December 31 of the last year in which you have date. Missing dates might generate wrong behavior in DAX Time Intelligence functions, which assume a year is always complete with all days in the Date table.

    • powerpivotpro says:

      Hi Marco. I’ve read the same thing before but have never stumbled on a case where this actually caused problems in practice.

      Actually, I’ve only encountered the opposite – where “Sales per Day” calculations were “unfair” to the unfinished most-recent year, for instance. (Not that such problems are insurmountable of course).

      Can you share a specific example where DAX time intel gets confused by a calendar table that ends before Dec 31? I don’t deny that such cases exist, just looking for the example(s).

      • Marco Russo says:

        PARALLELPERIOD could be one. If you use PARALLELPERIOD with a 0 offset you wouldn’t get the entire year (because it is truncated), then if you apply the result to ADDDATE you wouldn’t get the complete year. Imagine this happening in different filter context for different measures. If you control the entire formula it’s ok, the problem is when you have many nested measures – you might end up in unexpected behavior. I agree that if you strictly control the entire formula, you can avoid that happening.

  2. Dave H. says:

    When using Excel 2010 it appears that you cannot load directly to the data model from Power Query. Is there another workaround to get it into the Power Pivot model directly without having to load it to an Excel worksheet?

  3. Erik Dibbern says:

    Great post! Does the “refresh data when opening the file” mean that the power pivot model get refreshed when opening the file? For me that would increase the usability of a desktop power pivot by a hundred! If a (non excel pro) user can open a saved file with always fresh data.

    • Hi Erik,

      you can configure every connection separately. In the Connection Properties dialog (DATA – Connections – Connections) you should see all your connections.

      If you use Excel 2013, you could use VBA to refresh the data model. There is new object called “Model”. Try ThisWorkbook.Model.Refresh

  4. drew bambic says:

    Excel app developed by stepfan johansson — see http://www.stefanjohansson.org/ also works great

    Drew Bambic

  5. Andy says:

    Mike hi, regarding loading directly into the PP Model, within the Query settings I only have ‘enable download’ or ‘load to worksheet’, and if I disable the later then the connection is removed from the PP Model, suggesting that I have to load to the model via the worksheet. I also downloaded the PQuery but no ‘Load to Workbook’.

    • Mike Dietterick says:

      Sorry. It was a typo. ‘Load to Worksheet’ is the box that should be unchecked. Do this before trying to add the data to Power Pivot via a worksheet or otherwise. If you are using the latest version of Power Pivot for Excel 2010, you should see the Power Query data in Existing Connections. Not certain if this will work with earlier versions of Power Pivot.

  6. Cabby says:

    Mmmh, as soon as you start fiddling with custom calendars, it might still be necessary to create a date table in an Excel file.

    What I find useful doing this is that hat each change or newly created column is available in all other models that are connected to this Excel file. But then have to create and customize almost all dim-tables I use….

  7. Cristian Nicola says:

    When using this method, does the whole Date Stream get downloaded and then the Power Query filter gets applied to it “on-prem” or does it request only the range we try to get and therefore download only the relevant data?
    If it is the latter, this would make this feature super awesome (instead of awesome) and obviously applicable to more than just Dates.

    • Cristian, I think it qualifies as super awesome :-)

      After you click Apply & Close, the Workbook Queries task pane shows how many MB are being downloaded from DateSteam. If I don’t use any filter, it is close to 100 MB while with filters applied, it is much less. Therefore, it looks like only the relevant records are being downloaded.

  8. Cristian Nicola says:

    Does the Power Query apply the filter to the data once it is downloaded “on-prem” or does it make the request with the (dynamic) filter built in and thus only download the relevant records?

  9. David M. Evans says:

    Thanks for your insight into this technique.

    Am having an issue with the “Load to Data Model” not appearing in my Query Settings. Am new to this stuff, so I’m sure its a “duh” move on my part, but what exactly?!

  10. Chris Gilbert says:

    And here’s another option, purely through a PowerQuery script that you can generate a Calendar file, including ISO and 4-4-5 week “quarters” used in the retail world.
    http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

    I don’t usually use the full name for month_name and day_name, so I tried to augment the script to produce both long_names and short_names (January-December and Jan-Dec). I wasn’t successful when copying the existing long_name line code to create the corresponding short_name column. (If someone could enlighten me as to why that didn’t work, I’d appreciate it.), so I simply changed the “MMMM” and “dddd” format strings to “MMM” and “ddd”.

  11. Brent Alderton says:

    When I enter in the dynamic end to the end of the year as above, it only goes to the 31st of March, not even to the current month? Cant work out why! Any suggestions?

    • Brent Alderton says:

      Sorry, I thought it would be already sorted from oldest to newest. Sort showed it did indeed go to the end of the year. Great article!

  12. Ken Wetherell says:

    For 2010 users, it may be useful to set Power Query’s default settings to “Load to Data Model” only by unchecking the “Load to worksheet” box. These settings are found in the Power Query ribbon under “Options” –> “Specify custom default load settings”. Disclaimer: I am using 2013 and I have not tested whether these settings are available in 2010.

    • You are correct, Ken. In Excel 2010 there is no “Load to Data Model” checkbox, but you can uncheck “Load to worksheet”. By doing this, you create a connection which can then be found in Power Pivot’s “Existing Connections” :-)

Leave a Comment or Question