Date Table: If/When needed

[CahabaData]

I see alot of Q/A in the community where the requirement includes a Date Tabl that I don't think one is needed and this has me confused.  So I would like to state what I think is accurate and hope that this will be either confirmed or corrected.

Scenario 1: Your core table(s) have a date field that you will want to do aggregate summaries by Week/Month/Qtr/Year.

- For this no date table needs to be explicitly created nor imported because Power BI does that for you in the background.  This is per video tutorial: "https://www.youtube.com/watch?v=RiHpkN0gfPM"

Scenarial 2: Your core table(s) have a Start Date and End Date (.... or something equivalent) that bracket a range of dates. Your tables do NOT have every date between the bracketed (Start and End) range as they do not exist within any core table AND you need reporting by those in-between dates.  In this case you would need to create a date table that has every date that will span the first and last dates of your core table date range.

Correct?

[dMac314]

@CahabaData Great question! I was curious about the same thing. I've been able to create my reports without usiung a date table up to now and was wondering if I was doing something wrong/inefficiently. 

Thanks! 

[v-shex-msft · Accepted solution]

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

[CahabaData]

thank you XS for your input; because we numbered our examples differently let me re-organize & summarize what I understand:

Scenarios of Date Table Need:

1. No Date Table: core table(s) have a date field that you will want to do aggregate summaries by Week/Month/Qtr/Year.

- For this no date table needs to be explicitly created  ** I think this agrees with your last statement in your first post

2. Date Table: every date between the bracketed (Start and End) range as they do not exist within any core table ** I think this agrees with your #2 of your first post

3. Date Table: non-standard calendars ** introduced by KG input

4. Date Table; many to many relationships joined on date field ** introduced by your #1

5. Date Table: Use a slicer to filter multiple tables(contain date field) ** introduced by your #3

Right now I am confused on the point that the video displays the hidden date table to be joined to tables with date fields.  So to me it is exactly identical to #4 and #5 in its display.  I will say also that is confusing to me in reading that my confusion is due to the hidden date table being 'created by Time Intelligence Feature'....as this entire topic is about time intelligence.  If I make a Date Table manually and join it to tables with a date field it has the exact same appearance as the hidden date table.  Are you saying that the hidden date table display shown in the video is not acccurate?

Like many people I learn by real world examples, rather than theory - and so let me create an example:

Table Rainfall has many records, a date field, a field for the amount of rain field

Table UmbrellaSales has many records, a date field, a field for the amount of umbrella sales

 * I can create a Sum per month of Rainfall,  and a Sum per month of Umbrella Sales without making a Date Table

 * Can I put this information together in a single visual by month, or have visuals that interact/filter by month without making a Date Table?

[KGrice]

I tried your rainfall example by making the two tables. Each table had every date from July 1 2016 - September 13 2016. For rainfall, I used a random number between 0 and 5, and for umbrellas sold I used a random number between 0 and 50.

I didn't try relating them because I don't think it's a relationship that makes sense. I created the two simple measures to just sum up rainfall and umbrellas sold, then added the rainfall measure to the report canvas to create a chart, and added the Date field to the Axis. By default, it was aggregated by year, and looks nonsensical:

It took me a while to even figure out what the 2,000 and 2,500 were. They're years. Even though I only have 2016 data, it shows the axis with year 2,000 and 2,500. Hovering over the column shows Year 2016. Yuck.

Since we wanted to try by month, I exed out the Date hierarchy except for month. Looks better:

Then I added my Units Sold measure to the Values section of the chart and got this:

Well that's just wrong, and shows the total of the amount sold across all of the dates, and then extends that into months that weren't even on the chart. I understand the total, since there's no relationship between the two or a common calendar, but it doesn't "just work" without some intervention then.

And when I try dropping one of the Date fields on the canvas to make a slicer, my only option for the Date Hierarchy is by Year, so there's no way to filter in a slicer by month without at least a new column. You can however use the Date in the Visual level filter of the chart and filter out certain months.

I'm on the latest July release. Can't check right now if there were improvements for this in August. Maybe this would show the improvement if nothing else.

[CahabaData]

hmmm could have sworn I saw a tutorial on a similar example - need to hunt that down.... I thought it worked without an explicit date table but I see what you are saying...  will need to spend some time on this...

[v-shex-msft]

Hi @CahabaData,

Does @KGrice's solution worked?

Regards,

Xiaoxin Sheng

[CahabaData]

@KGrice    What I found is that to make a single visual with both Umbrellas Sold & Rain Amount as values along a common time line axis - - that I had to join the Date fields of the 2 tables together.

In my mock up both tables had every date - so that a join is going to work in terms of providing all data.  If both tables did not have all the dates then some of the data would not be included and that would be a problem.

In the tutorial video (URL given in my original post) the speaker displays a hidden automatic Date Table.  I guess it's role is limited to providing the embedded Time Intelligence functions so that if one had a single table without all dates - that you do not need to create a Date table for Time Intelligence.

However in a multi table scenario one needs all dates.  If the core tables do not provide those then a Date Table needs to be created....which is what XS's post stated.

[KGrice]

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.

[CahabaData]

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...... 

[KGrice]

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.

[CahabaData]

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.