Date Heirarchy won't create

[joglidden2]

I have a date field, and yes, it's set to date data type under column tools. I have the obscure little 'Time intelligence' box check in settings. Data source is direct query. Every time I go to generate a heirarchy, it just duplicates itself. I can't get Year, Quarter, Month, Day. 

What am I missing? 

[v-yalanwu-msft · Accepted solution]

Hi, @joglidden2 ,
https://community.powerbi.com/t5/Community-Blog/Why-there-is-no-date-hierarchy/ba-p/2266682

Now let's see what we need to be aware here regarding the date hierarchy.

• Enable automatic date and time
• The table storage mode is Import
• The column data type is date or date/time
• The column isn't the "many" side of a model relationship

When the above conditions are met, the date hierarchy will work for you.
because your data source is Direct query, so it won't wok.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joglidden2]

Microsoft Idea (powerbi.com)

[joglidden2]

That's the issue: table storage must be import. Therefore, Time Intelligence in PBI is useless to me. I'll put in a request to the PBI team to add this capability. Thanks for catching that. 

[v-yalanwu-msft]

Hi, @joglidden2 ,

Can you share your date column data or screenshots? Perhaps the text is not formatted to date.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joglidden2]

It is formatted to date both in PBI (shown), and SQL Server (not shown). Direct Query. 

[v-yalanwu-msft]

Hi, @joglidden2 ,

If you have not enabled this option, then you need to enable this option first.

In Power BI Desktop, you select File > Options and settings > Options, and then select either the Global or Current File page. On either page, the option exists in the Time intelligence section.

Reason 2:

When a date field is related to another non-date field (such as an integer type Date Key field). Power BI still removes the hierarchy of fields on Many side of a one-to-many relationship.

https://community.powerbi.com/t5/Community-Blog/Why-there-is-no-date-hierarchy/ba-p/2266682

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joglidden2]

Good question, and thanks for the link. Yes, the table that I'm trying to create the date heirarchy on is on the many side of a one-many relationship. However, it is not the date field that forms the relationship. See screenshot: in red I've underlined the integer fields that form the relationship, and in blue the date field that I'm attempting to create the heirarchy with. According to the article link, this should still be allowed. 

[amitchandak]

@joglidden2 , Please make sure it is checked at the report level. If yes, it should generate for all the dates not joined to the date table (marked as date table).

Make sure you are using latest version of power bi and no windows patch is pending

[joglidden2]

Yes, good question. 'Time Intelligence' was checked for both current file and global. I should have been clear that I checked both.