Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 ReportingThanks 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHere'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.
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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |