Creating my own Date Table

[PaulKraemer]

Hi,

I've been trying to learn Power BI using the Microsoft learning paths available through the following link ...

https://learn.microsoft.com/en-us/training/browse/?products=power-bi

... From what I've read, it seems like it will be to my advantage if I create my own Date table to allow for filtering and grouping my visuals by Fiscal Year, Quarter, Month, etc rather than relying on the Time Intelligence "Auto Date/Time" option.

A few of the Learning Path's that cover this topic are based on an "Adventure Works" example database.  In the examples I've seen, the Date table was already created.  These examples did not explain exactly how this Date table was created.  I've learned that I can used the CALENDARAUTO DAX function to create a calculated table with a single column of type Date that includes a contiguous set of dates that encompasses the range of dates present in my model.

I can figure out how to add calulated columns for the groupings I am interested in like fiscal year, Quarter, Month, etc, but before I go too far with this, I have a question.

In the Adventure Works examples that demonstrate filtering and grouping based on date, they do not seem to use the column of type "Date" for the relationships between the Date table and the other tables in the model.  Instead, they base these relationships on a column named "DateKey" with the date represented in the format "YYYYMMDD".

In my actual database, all of my date fields are saved as type "Date".  If I create my own Date table, should I be able to establish relationships between my Date table and my other tables based on the fields with type "Date"?  Or should I copy the Adventure Works examples and make use of a date representation in the format "YYYYMMDD".  It seems this would require me to create calculated fields in all of my model tables to represent dates this way.  This would take some time, so I'd rather not do it unless there is a good reason for it.

Any advice will be greatly appreciated.

Thanks in advance,
Paul

[divyed]

Hello @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

[PaulKraemer]

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

[divyed]

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,

[OwenAuger · Accepted solution]

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!

[PaulKraemer]

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

[OwenAuger]

Hi @PaulKraemer 

In a Power BI semantic model, a column of type date is perfectly fine for relationships with a Date table. In fact, I would encourage it!

This SQLBI article provides a good analysis of the question of whether to use a date or integer (e.g. YYYYMMDD) column. Also see this video.

Key points:

• In a Power BI semantic model, there is no inherent difference in performance or storage based on whether columns of type date or integer are used.
• If fact tables represent dates using columns of type date rather than type integer, there is an advantage in terms of convenience of authoring calculations that need to refer to date values from those fact tables. If an integer column is used, it may need to be translated to a date value via the relationship with the Date table, which can result in some awkward code (and possible performance impact).

Taking this into account, I normally stick with date column types for all relationships with Date tables.

I understand that the use of an integer keys may be a more important consideration in certain databases, and has been something of a historical convention, but I don't think this should influence the choice of column types in a Power BI semantic model.

Regards