skip to Main Content


“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 [link removed due to 404].


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 original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 43 Comments
  1. 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. Boyan,

      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.


  2. 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.

  3. Ouch, that UI comment hurts. Seriously enough, could you resend your UI feedback to [email protected]?

    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 🙂

  4. 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

  5. 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.



  6. 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?

  7. 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 ?


  8. 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. 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. 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. 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 FilesMicrosoft OfficeOffice14 and just copied a file in there and named it noted above. Now, I am ready to blow some people minds!

  9. 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!

  10. 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 🙂

  11. 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!

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

    Could you help me?

  13. 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?

  14. 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.

  15. 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.

  16. Thanks for the table. Now I just have to tweak it as I’m in one of the many organization that uses fiscal years. I imagine it’s just a matter of creating more columns for Fiscal Year, Fiscal Month (fiscal weeks) and connecting those to the actual date field which is the same in a fiscal or regular calendar logic.

  17. In PowerBI Desktop, you can use CALENDAR(MINX(‘Sales'[OrderDate]),MAXX(‘Sales'[ShipDate])), and it will generate a calendar hierarchy (albeit limited) for you… and then you can follow along with F & R’s book and build out your other columns… I wonder when/if that will be included in Excel… (could be in 2016… don’t know!)

  18. What options are available now that this one will be eliminated at the end of March 2017? My labor efficiency report is tied to this and it no longer works. Thanks!

    1. Personally I like the Power Query method best (well, I like the “have a database pro give me one” method even better, but failing that…)

      I really like using PQ since it lets me constrain the dates to perfectly match my latest data.

      It’s in the PQ chapter of the 2nd edition of our book, but I’m sure you can also find it via Dr. Google (maybe even on this site, but if not, definitely on other sites).

  19. I am working with monthly data, no sales, but data related to Health and Safetyand want to compare YTD with months and years. Do not know waht kind of calendar I should use to get these comparisons? 🙁

  20. I’m still having this issue after the Excel.exe.cfg file and I can sign in to that page i.e. I think I have an account) .. any other thoughts / solutions? I’m using Excel 2010 with the latest PP and PQ addins (downloaded Friday Aug 11)


Leave a Comment or Question