The Ultimate Date Table


“Looks like it’s time for me to get myself a date.”

-Ace Ventura, PowerPivot Detective

The Importance of a Date/Calendar Table

I get a lot of questions from people who are struggling with the time intelligence functions in DAX.  And nine times out of ten, the answer is that they don’t have a proper date table.

I know it’s tempting.  You’ve got your sales table, and hey, there’s a Date column in there!  So you use it, and pass that column as a parameter to, say, DATESBETWEEN, or DATEADD.

Sometimes that will give you an error.  And other times, it won’t…  but the results will be funky.

You need a separate Dates table, or perhaps you prefer to call it a Calendar table.  A separate table, whose only purpose is to store dates (and the properties of dates, like DayOfWeek, etc.)  And it contains consecutive dates – no “gaps.”  Even if your business is never open on weekends, you need unbroken ranges of dates.

Oh, and then you need to relate it to your Sales table.  (Or whatever fact/measure tables you have).

Much More Than a Single Column

A single-column table that contains merely dates is enough to make the time intelligence DAX functions operate smoothly.  But you will almost certainly want other fields too.  Like Year.  MonthName.  DayOfWeek.  The list goes on.

Maybe something like this:


And yes, you can cobble this together on your own in Excel.  Tedious work though.

Would You Like One for Free?  Try DateStream from Boyan Penev!

Imagine just being able to open up PowerPivot and always having three nice date tables awaiting import:


That’s what Boyan Penev has put together for you.  Three great calendar tables that you can download directly into PowerPivot, for free.

He published them to Azure DataMarket, a service from Microsoft where data providers can actually sell you their data sets – things like weather, demographics, etc.

Boyan did this for free though – I suspect half as a service to the community, and half as a project to learn how to provide a service on DataMarket.

It’s pretty damn cool, and really, the story should end there.  If you’ve used DataMarket before, then it DOES end there.  Go get the date tables and try them out.

But if this is your first exposure to DataMarket, it takes a few minutes to get it set up.  It’s not bad as long as you don’t make the mistakes I did.

How To Get It – Short Version

Hey, it’s on Azure DataMarket.  The URL is in the next section below, or you can just go to Azure DataMarket and search on “DateStream.”

DataMarket is going to be a wonderful service someday, but right now it has a few warts, so there is a Long Version too.

How To Get It – Long Version with Occasional Snarky Commentary

Step 1:  Go to the DateStream page on DataMarket.


Step 2:  Get confused.  OK, now is where things get choppy, because frankly, the DataMarket site itself has a terrible user interface.  I sent a full page of feedback to the DataMarket team about a month ago and as far as I can tell, they ignored it.  (Which is pure karma – I used to be one of the people at MS who ignored 90% of the feedback coming in, and now I get to be the one who is ignored).

I don’t want this to be a tutorial on how to navigate their website, or even how NOT to design a website.  So let’s just hit the highlights and try to get to Boyan’s date tables as soon as we can.

Step 3:  Get an account.  OK, this isn’t bad.  Another MS site that requires a Live ID.  Most of us have three of those by now.

Step 4a:  Scan the DateStream page looking for the “Download to PowerPivot” button or link.

Yeah that’s right.  There is no such link – you can get to one by navigating a few levels deeper but I’m going to skip that.  Don’t despair though, good things await you!

Step 4:  Find the URL of the DateStream Service

This is NOT the same as the URL of the DateStream page.  But it IS displayed on the page.  Here’s the URL you need:

OK, copy that.  You will need it.

Step 5:  Launch PowerPivot, Go Into the PowerPivot Window

And click this button:



If you don’t have that button, you need a newer version of PowerPivot.  Go get that from and resume the next step.

Step 6:  Fill in the Dataset URL From Step 5


Step 7:  Account Key

See that last text box in the picture above?  The one with the long code in it that I’ve partly blurred?  That’s my account key.  I highly recommend clicking that Find button.  It’s actually pretty damn useful.

Be careful – the DataMarket site has TWO long nasty codes like that for you.  One of them is the one you want, and the Find button takes you to that one:


THIS is Your Account Key

Do NOT, under any circumstances, do what I did, and confuse Account Key with Customer ID:



This is NOT Your Account Key.
Do NOT Be Tempted to Use This!

Step 8:  Click Next, and Pick Your Table or Tables


NOW we are on familiar ground.

Last Note:  Parameterization?

One thing I have not yet figured out is how to limit the date range I import.  The table starts in the year 1900, which goes back a bit far for my needs, and makes the dataset take a long time to download.

You’ll notice that when importing from DataMarket, the Preview and Filter UI lacks the filter dropdown buttons:


No Filter Dropdowns, Just Checkboxes

But the DateStream homepage DOES indicated that parameterization is possible:


So if you’ve got that figured out, drop me a note Smile

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 34 Comments

  1. Dan English

    Boyan has a post on how to parameterize the URL to get a range of dates. You can check it out here and he has quite a few examples based on what you are trying to do –

  2. Boyan Penev

    Thanks for the post Rob!

    First, you can filter the date range. Have a look at:

    It’s very fiddly, but it works. And..I got an email from a DateStream user telling me they implemented the range querying and it works for them, so give it a quick go. Importing less than the whole feed of 2 centuries of data is much faster, too.

    Also, there are four more localised tables coming – in Hebrew, Danish, German and Bulgarian. Please let me know if any of your readers would like to participate and translate the feed in other languages as well. The goal is to have the whole world using it, after all :)

    1. timrodman


      Thanks for this table. Also, thanks for the tip about filtering the date range. I discovered something in the marketplace (maybe it’s new), that allows you to more easily create the hyperlink that you want.

      1. Go here:

      2. If you have subscribed to the feed using the “SIGN UP” button mentioned in the other comments on this blog, you should see three words to the right of the feed: “data”, “subscribed”, and “use”. Click on the word “use”.

      3. The available tables are now listed on the bottom of the screen and you can filter on the columns or even exclude certain columns if you don’t want them. Just use the down arrows next to each column name.

      4. After you set your filters and column exclusions, the URL is automatically created for you in the area above the table. You can use this URL in the “Data Feed Url” field when you bring the feed into Power Pivot. Your data will be filtered and columns will be excluded.

      5. To combine filters on the same column, like a year is “greater than or equal to” and “less than or equal to”, you still need to dissect the URL a little bit. Just put a %20and%20 in between the filter criteria.

      This is the same method that you mention here:

      It’s just a nicer graphical way to accomplish it.


  3. Ben Niebuhr

    I took the advice to use a dimDATE table right away, but in my field, we work with hourly granularity. I have had some trouble with getting some of the calculations to return as I expect. I freely admit that it is probably user error, as I only started using PowerPivot a few months ago.

    Does anyone have any tips for working with sub-daily data. I have been using an hourly date table, complete with all the applicable descriptions. I have especially had trouble with the fact that my database is in GMT, but I would like to have everything totaled in EST (it is a 24hr data stream).

    Any help would be appreciated. And thank you for offering such a rich resource in this blog. I read every post.

  4. Max Uritsky

    Ouch, that UI comment hurts. Seriously enough, could you resend your UI feedback to

    I saw at least some part of it on the MVP group list, but I want to make sure we get the full one.

    We are working on improving some of the user workflow and performance of PowerPivot and DataMarket integration, so expect some thing to change soon… The are two good points to note: it actually works and we actually listen :)

    1. powerpivotpro

      Thanks Max, will do!

  5. jakob

    After selecting BasicCalendarUS and pressing the Finish i got the following error:

    “The remote server returned an error: (403) Forbidden.”

    What does that mean?

    Kkv. Jakob

  6. Sven

    Same error for me. Anybody knows how to fix that?

  7. Gjor

    When i try to download the BasicCalendarEnglish published by Datestream , I’m getting the following error “BasicCalendarEnglish: The remote server returned an error: (403) Forbidden”. The publishers is telling me that there are not problems at his end.

    Any help will be greatly appreciated.



  8. Adam

    When trying to connect, I get the following error:
    “The remote server returned an error: (407) Proxy Authentication Required”.

    I’m guessing that this is because the company I work for has some sort of network security settings preventing me from connecting.

    Does any of you have a work-around, or an alternative way of “creating” a dynamic date master table?

  9. Idan Cohen

    I am getting : “BasicCalendarEnglish: The remote server returned an error: (401) Unauthorized.”

    i already managed to connect from another powerpivot workbook with the same account.

    Does any one have a solution ?


  10. Neil

    This is my error: Cannot connect to the specified feed. Verify the connection and try again. Reason: The remote server returned an error: (407) Proxy Authentication Required.

    1. Paul

      Hi Neil,
      I have the same problem, did you find a solution?

      1. Kyle Hale

        Late to the party here, but the solution is to (no joke) create a file called excel.exe.config in the folder where your EXCEL.EXE file sits. It needs the following contents in it

        Restart Excel and you’re good to go.

        1. Kyle Hale

          Sorry, WordPress (probably rightfully so) removed the contents, here it is except I removed the opening tags from each line, so add those back in:

          ?xml version=”1.0″ encoding=”utf-8″ ?>
          defaultProxy useDefaultCredentials=”true”>
          proxy usesystemdefault=”true”/>

          1. Young Salsa

            6/4/2014 Update. I can confirm the excel.exe.config file worked. I just copied a file from where excel.exe sits which is here. C:\Program Files\Microsoft Office\Office14 and just copied a file in there and named it noted above. Now, I am ready to blow some people minds!


    Same error for 403 forbidden like everyone else. Any ideas ?

  12. powerpivotpro

    I’ve asked Boyan to check in on this thread, he may have an answer.

  13. John H

    The API URL listed on the blog post is no longer correct — make sure to use the one listed on the Azure site.

  14. Will B

    I got the 403 Forbidden error. After clicking “sign up” though on the Azure page for DateStream (it’s $0.00/mo) it let me access fine. It seems your account key is used to validate access to streams you have signed up for… Pretty confusing!

  15. powerpivotpro

    Everyone on this comment thread to date who has been struggling with errors has now been connected with Microsoft to see what the problem is.

    Hopefully someone will report back on what they find out :)

  16. kcsteven

    I’m seeing the 403 error as well.

  17. PedroCZ

    Guys, any updates on this. Do you have any alternatives?

  18. Tim Siegenbeek van Heukelom

    For the 403 error, this solved it for me:

    Make an excel.exe.config file with below code, and put it in the same directory as excel.exe

    1. powerpivotpro

      Looks like your code got stripped by WordPress, Tim :(

  19. mandeep

    if you get the 403 it means that you have not subscribed to the data feed. That is the step you are missing

    1. timrodman

      Thanks mandeep. I was having the same problem. Clicking the “SIGN UP” button in the upper-right-hand corner of underneath “$0.00 per month” solved the problem.

      I guess you have to sign up, even though the feed is free.

  20. Santi Azpilicueta

    Hi Rob,

    Thanks for the great info you provide… I love these tables, except they are huge! I finally figured out how to get a filtered feed (even with the new changes to the Excel 2013).

    The trick is to edit the Azure connection, select advanced, and go to “Inline Service document”, where you will add the filter to each of the URLs in the form of a query string… Note: I copy the very long string and work with it in a Notepad(++)…

    An example filter from 2010 to 2030 will look like this:


    So a partial section of that line could look like this for example:

    For more detailed information look at this Boyan Penev post:

    I hope this is of value for someone. It took me some time to figure it out for 2013!

  21. Eloi Baixeras

    Sorry but I couldn’t download your table, I get on the internet a message with error 403 error.

    Could you help me?

  22. serky

    Thanks for this very useful blog. I successfully connected to Azure from Power Pivot and downloaded the DateStream table but had one small problem. I used timrodman instructions from July 26, 2013 at 4:09 pm to filter the data to year >=2000. This worked perfectly in Azure but Power Pivot downloaded all the data ie – from 1900. Eventually I had to save the filtered date set as a csv and load to Power Point separately. So my questions are:
    1. how do I get Power Pivot to recognize the filtered data in Azure?
    2. how do I get Azure to save my filter instructions so I don’t have to re-enter the filter each time I log in to Azure?

  23. Natalie Mickan

    I signed up for an account and subscribed plus I created the excel.exe.config file as described above and still nothing works. In fact, I had to go back and delete that new config file because I couldn’t open Excel after that. Still just get an ‘unauthorized’ error when trying to get the date files. I would still like to use them though if there are any other suggestions.

  24. Natalie Mickan

    Never mind….the azure marketplace link may not work in PowerPivot but you can filter and download the file, which is what I did.

  25. Shawn

    Is there a way to permanently disable refreshes with pivot tables or the connection? I have tried removing the connections; however, that deletes the entire date table from powerpivot and breaks all the pivot tables.

    Since this is a date table, there wouldn’t be a need to connect to the datastream after the initial download. The datastream date table is linked to several other tables which are used in pivots and the refresh times becoming problematic with the amount of rows imported from the datastream.

Leave a Comment or Question