Forum Discussion
Advocate IICreating 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!
Super UserHello PaulKraemer ,
Thanks to OwenAuger for providing details what to use and why, I would like to add few more point why should we use DATE type than INT :
1. When you use the DATE column, Power BI can automatically recognize it as a date and offer all the built-in time intelligence capabilities.
Even though DATEINT is numerically equivalent to a date , it isn't recognized as a true Date type by Power BI. This means you won’t get the same built-in time intelligence functions that are native to date columns. You would need to manually convert it to a date in DAX if you want to perform date calculations, and you'd miss out on features like automatic date hierarchies
2. Date Hierarchies:
-
Date type columns automatically allow Power BI to create date hierarchies like Year, Quarter, Month, and Day. This makes it easy to break down data by different time periods without having to manually create calculated columns or complex logic.
-
DateInt columns would not automatically create such hierarchies. You would need to build those hierarchies yourself by extracting the year, month, quarter, etc., from the integer value, which can add complexity to your model.
3. Simplicity and Readability:
-
Using a Date column improves the clarity and readability of your model because it’s semantically clear that the column represents a date. This also helps others working with the model (or when revisiting the model after some time) to understand the intent of the column.
-
DateInt is less readable for someone unfamiliar with the model, as it's just an integer (e.g.,
20230101), which could be confusing.
4. Flexibility with Time-Based Calculations:
-
Date type columns allow you to do more flexible and dynamic time-based calculations directly within Power BI without having to do any transformations or conversions.
-
DateInt would require additional steps if you want to perform more advanced calculations, especially if you need to manipulate the date values to extract different parts (like month, quarter, etc.).
I hope this helps.
Did I answer your query ? Mark this as solution if this helps.
Warm Regards,
Neeraj Kumar