Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

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

So if your original line is like this, you just need to use some functions to determine the dates vs hardcoding.

={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))}

 

This will always give you a rolling 6 months. I've inserted a lot of line feeds to make the formulas a bit easier to read, but you could type that Source line all on one line. The key to all of this is DateTime.LocalNow() - that is equivalent to @NOW() in Excel - the current date and time from the system clock.

let
    Source = 
        {
            Number.From(
                Date.AddMonths(
                    DateTime.Date(
                        DateTime.LocalNow()
                    ),
                    -6
                )
            )..
            Number.From(
                DateTime.Date(
                    DateTime.LocalNow()
                )
            )          
        },
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}})
in
    #"Changed Type"

  



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

24 REPLIES 24
Anonymous
Not applicable

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.

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
Anonymous
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.