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 OwenAuger and Neeraj -
Thank you very much for your thorough replies. As all of the Date columns in the Data Warehouse I am working with are of type Date, I will use a column of type Date to establish the relationships with my Date table.
I do have a follow up question. One of the advantages Neeraj pointed out for using Date columns is the automatic creation of hierarchies.
After I read the following article ...
https://learn.microsoft.com/en-us/power-bi/guidance/auto-date-time
... I got the impression that it might be to my advantage to turn off the auto-date-time option and use DAX to create my own Date table. I used CALENDARAUTO to create a calculated table. Then I added calculated columns for FiscalYear, FiscalQuarter, Month, etc.
As I start building my data model and creating some visuals, I would plan to establish relationships between this common Date table and my other tables that contain Date fields.
I am just getting started with Power BI, so I am sure there will be alot I can learn if I take this approach and fail, but if there is a different approach you would suggest, I would greatly appreciate it.
Thanks again!
Paul
Hello PaulKraemer ,
Yes , we should disable auto-detect for datetime and choose data types wisely until necessary . I strongly agree with a separate date table if possible . In previous response I was talking about separate date table only and choosing date column as type DATE.
Power BI automatically detects datetime columns and creates a hidden date table for each one, which could increase memory usage and slow down your model. By disabling this feature, you avoid creating unnecessary date tables that are not needed for your analysis
Warm Regards,