Forum Discussion
Creating my own Date Table
Hi PaulKraemer
I would also recommend turning off auto date/time as a general rule, in line with what you've read.
If enabled, it creates a hidden table for every date column in the model (excluding calc tables/columns), which can significantly bloat the model size and harm performance.
I would recommend defining hierarchies yourself in your own Date table, whether created with DAX or Power Query.
As far as how to create the Date table:
- CALENDARAUTO may work fine in simple models, but because it searches the model for date columns and expands include all years spanning dates in those columns, it may become larger than required.
- There are various Power Query date table functions online. See here for example.
- If using DAX, I recommend instead using a pattern similar to this:
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ - You can also use the Bravo external tool to create a date table.
- If you would like to see an example of a Date table with hierarchies defined, you could download one of these Contoso Data Generator models from here.
For example download "pbix-100k.7z".
Hope that helps!
Hi PaulKraemer
I would also recommend turning off auto date/time as a general rule, in line with what you've read.
If enabled, it creates a hidden table for every date column in the model (excluding calc tables/columns), which can significantly bloat the model size and harm performance.
I would recommend defining hierarchies yourself in your own Date table, whether created with DAX or Power Query.
As far as how to create the Date table:
- CALENDARAUTO may work fine in simple models, but because it searches the model for date columns and expands include all years spanning dates in those columns, it may become larger than required.
- There are various Power Query date table functions online. See here for example.
- If using DAX, I recommend instead using a pattern similar to this:
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ - You can also use the Bravo external tool to create a date table.
- If you would like to see an example of a Date table with hierarchies defined, you could download one of these Contoso Data Generator models from here.
For example download "pbix-100k.7z".
Hope that helps!
Thank you Owen - this is all very helpful !
I will be able to adapt my DAX (which currently makes use of CALENDARAUTO) to limit the range of dates to the ones I'm actually interested in for reporting purposes. I hadn't thought about the option of doing the same thing in Power Query, but I will try that also. (As I started trying to learn Power BI, I focused more on DAX than Power Query, but I'd like to become competent with both.)
Bravo seems like a useful tool also.
I do have a follow-up question about the Contoso sample you mentioned. I can see that there are hierarchies defined in Power BI. I was curious to see whether the Date table was created using DAX or Power Query, but when I opened Power Query Editor, it looks to me like the Date table is defined in an underlying SQL server database for which I don't seem to have credentials to access. Is the intent with these sample databases just to provide Power BI examples with data that was pulled from this database before the sample was made available? Or is it possible to connect to these back-end tables (for example to refresh report data).
I really appreciate your help.
Thanks again and best regards,
Paul