cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdriscol
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.

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

19 REPLIES 19
Ashish_Mathur
Super User
Super User

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/

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))

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/

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!

You are welcome.  Thank you for your kind words.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
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

Hi @edhans 

 

What do you means by "4. Convert it to a table (upper left menu button." please eloberate it is not clear? 

The first 3 steps generate a List, not a table, so it looks like this:

edhans_0-1622132332771.png

 



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

Hi @edhans 
What do you means by (4) Convert it to a table (upper left menu button , this not clear senince could you eloberate it? 

@edhans I've got a new adaptation I need. 

I have created your mquery date list, but instead of hard coded dates, I want to bring in the previous 6 calendar months, up to and including today.

Go.

 

Jemma 🙂

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

@edhans thanks for getting back to me.

This is almost perfect. I need the full calendar six months, not a rolling 6 months. So I want it to work out that it should start on the 1st September, not 26th September. Is that possible?

Jemma

Yes. Just wrap that result with Date.StartOfMonth() and it will go back to Sept 1. So it would be Date.StartOfMonth(Date.AddMonths(....



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

Thank you so much for your help @edhans this has worked for me! 🙂 

Great @heytherejem - glad it helped. I'll add this to the "solutions" for this thread if you don't object.



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

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

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.