Forum Discussion
Date Table: If/When needed
Hi CahabaData,
Based on you description, you want to know whenever you need to use a date table, right?
If as I said, you can refer to below scenarios which you need to use date table:
1. many to many relationship.
If you have two tables both contain many duplicate date records and you want to create relationship between them, you should add a date table to link them.
2. Get specify date between start date and end date.
Sample: get the weekend from 2010 to 2015.
3. Use a slicer to filter multiple tables(contain date field).
>> the hidden automatic Date table and it automatically joins to all tables/fields that have a date field type...
You have a misunderstanding of the hidden table, the hide table is created by time intelligence feature, the hide table(date hierarchy) is analysis by date field, it only related to the column which used to create the table, not automatically joins to all tables/fields that have a date field type.
In addition, you could work through ‘Scenario 1’ without create a date table , the time intelligence feature is enable on the newest version of power bi desktop(2.38.4491.282).
Regards,
Xiaoxin Sheng
I think that's right, at least mostly. The only thing I'm not sure of is in Scenario 1, if your table does not contain all dates. Power BI can automatically do aggregates based on Date hierarchies created behind the scenes, but I'm not sure if all of the Time Intelligence functions will work properly without a continuous range of all dates. Sorry if that's addressed in the video; can't check it out at the moment.
You could also benefit from a date table in Scenario 1 if you have multiple tables that have different date references and you want a common date table to join to. This would be especially true if you are referencing aggregate or informational fields like Quarter, isWeekend, etc. that are not automatically created behind the scenes and could require you to make them multiple times, once in each table if there's no common relatable field. For example, a Sales table containing Transaction Date and an Employee table containing Hire Date.
thanks KG - so in the video - he displays the hidden automatic Date table and it automatically joins to all tables/fields that have a date field type...
I believe this addresses your second paragraph example of a Transaction Date and Hire Date ......but...... not sure.
In sum - I see alot of Q/A where the post/user seems to be default expecting an explicit Date Table must always be made manually. And in most of these cases it is not involving in-between dates of a bracketed date range and so I don't think they are needing to do this. There is no harm in it - but it is not needed... at least that is what I think......
I'll hopefully watch the video today and tinker with what can be done without the date table. I think for anyone transitioning from years of Power Pivot has it ingrained to always create and reference a date table, but it sounds like some or most of that dependency is gone. Based on this community idea (with work already started), there will be more improvements in this area, especially for dynamic date ranges. That will be particularly nice. I remember having to make measures or columns to handle all of those things (last 7 days, this month, etc.).
Another reason for an explicitly created calendar I remembered: non-standard calendars, like a 4-5-4 retail calendar.
Good discussion to bring up.
Agreed on non standard calendar. That needs to be scenario 3 where a Date Table is needed. And this may be the biggest reason to do it because of holidays versus workdays where holidays are unique.
Will be interested in your advice on the auto hidden date table.
