Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
IMHO, the fastest way is:
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,
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSo 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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAnother 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()))}
Hi,
You may go to Data > Modelling > New Table and enter this formula
=CALENDAR(MIN(Data[Date]),MAX(Data[Date]))
Hope this helps.
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:
Hi,
That New Table formula seems correct except for the bracketing
Calendar = CALENDAR(MIN(GCRTDatabase[Received Date])-182,MAX(GCRTDatabase[Received Date])+182)
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.
IMHO, the fastest way is:
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,
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAh right, ok.
But why does making a Field the date table remove the date hierarchy?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSigned in to say thank you!
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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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(....
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |