cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Creating Date Tables

As of today (10/26/18), what's the most convenient way to create date tables in BI? Apparently there used to be a feature whereby one only had to click a single button to drop a date table into the model, but apparently it's been moved or disabled outright. Thanks for any feedback.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

IMHO, the fastest way is:

  1. Open a blank query in Power Query of Power BI
  2. type ={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))}
  3. That will generate a series of numbers as a list
  4. Convert it to a table (upper left menu button.
  5. Convert the ABC123 type to date
  6. Rename to Date.
  7. Now you have a date table. Add columns as necessary (year, month, month name, etc) to make your date table suit your needs.
  8. Close and load.
  9. Right-click on it and mark it as a date table.

 

If you have source data with dates in it, get fancy and find the earliest date in your data, then make row #2 above be Jan 1, YYYY where YYYY is the earliest date in your dataset,



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

10 REPLIES 10
Highlighted
Super User III
Super User III

IMHO, the fastest way is:

  1. Open a blank query in Power Query of Power BI
  2. type ={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))}
  3. That will generate a series of numbers as a list
  4. Convert it to a table (upper left menu button.
  5. Convert the ABC123 type to date
  6. Rename to Date.
  7. Now you have a date table. Add columns as necessary (year, month, month name, etc) to make your date table suit your needs.
  8. Close and load.
  9. Right-click on it and mark it as a date table.

 

If you have source data with dates in it, get fancy and find the earliest date in your data, then make row #2 above be Jan 1, YYYY where YYYY is the earliest date in your dataset,



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted

Thanks for the tip. However, do you know if there was ever an automatic date table feature in BI? I know for sure there was a date table you could drop into Power Pivot for Excel 2016.

Highlighted

It will do automatic dates but you have little control over it. I 100% of the time disable the automatic date options in Power BI and roll my own date table.

 

Where in Excel 2016 can you do an automatic date table? Short of the CALENDARAUTO() DAX function. You could also do this in DAX with the CALENDAR() function, but I prefer to create my tables in Power Query. They are more compact. If you do it in DAX you have calculated columns, which I try to avoid.

 

And CALENDARAUTO() is very dangerous. If you have somethign like a marketing campaign table with bogus expiration dates like 12/31/2999, which is actually not rare, CALENDARAUTO() will create a full calender table 900 years out!

 

If you are talking about another way to do an automaticl calendar in Excel 2016, I'd be interested in hearing it.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted

Here's a link to an edX class I took about a year ago. This video shows adding date tables directly within PowerPivot in Excel 2016.

 

edX link

 

If the link doesn't work (or if you don't have a login for edX), in the PowerPivot window, go to the Design ribbon, and there is an option under the Calendars section for new date table.

Date Table.JPG

Highlighted

Ahhh... interesting. I've never tried that as I always have a date table from Power Query. That is just a macro with a few pre-defined calculated columns. Could be handy, but depending on the size of your model, a DAX based table will slow the model down and bloat the file up.

 

But no, Power BI doesn't have that feature.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Super User III
Super User III

Hi,

 

You may go to Data > Modelling > New Table and enter this formula

 

=CALENDAR(MIN(Data[Date]),MAX(Data[Date]))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

Hi @Ashish_Mathur 

 

I just came across this and it's great, I wonder if I wanted to add 6 months before and after my chosen date (to widen the scope if I want to use another date in my dataset) - how to I go about this? 

 

I tried the following which doesn't work:

 

Calendar = CALENDAR(MIN((GCRTDatabase[Received Date])-182),MAX((GCRTDatabase[Received Date])+182))
Highlighted

Hi,

That New Table formula seems correct except for the bracketing

Calendar = CALENDAR(MIN(GCRTDatabase[Received Date])-182,MAX(GCRTDatabase[Received Date])+182)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

Hi @Ashish_Mathur 


That worked perfectly thanks! I did play with the brackets a little but clearly didnt get it quite right 🙂

You're a star!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors