Creating Date Tables

[Anonymous]

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.

[edhans · Accepted solution]

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,

[Arjit_Sharma]

Another way to create a date table using Power Query in Power BI is by using the following query. It will generate a list starting from September 1, 2017. You can replace the start date according to your needs and create a list of date numbers.

= {Number.From(#date(2017,9,1))..Number.From(Date.From(DateTime.LocalNow()))}

[Ashish_Mathur]

Hi,

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

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

Hope this helps.

[Anonymous]

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

[Ashish_Mathur]

Hi,

That New Table formula seems correct except for the bracketing

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

[Anonymous]

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!

[Ashish_Mathur]

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

[edhans · Accepted solution]

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,

[Anonymous]

I have done this precisely as described above, but when I mark it as date table in the main report I am no longer able to create a Date hierarchy. What am I doing wrong? 

[edhans]

right-click on a date field and select Create Hierarchy, then right-click on a second date field and add to that hierarchy, and so on. 

[Anonymous]

Ah right, ok. 

But why does making a Field the date table remove the date hierarchy?

[edhans]

Shouldn't. Turning off "Automatic Date/Time Intelligence" does turn off the automatic hierarchy, and that is a good thing. You do NOT want a date hierarchy (which is a hidden date table) for every date field in your model. Build your own hierarchies as needed - which don't create hidden tables.

[GonzaloB]

Signed in to say thank you!

[MAAbdullah_47]

Hi @edhans 

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

[edhans]

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

[MAAbdullah_47]

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? 

[Anonymous]

@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 🙂

[edhans · Accepted solution]

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"

[Anonymous]

@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

[edhans]

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

[Anonymous]

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