No Date Hierarchy on Date fields

[Anonymous]

Hi all,

I've created a "Date" table as reccomended by multiple folks for dealing with models where time analysis is required. For some reason the Date field has no Date Hierarchy...? This is also happening on other fileds in other tables that are clearly "dates". They show as dates in the field properties and in Power Query they are very clearly formatted as dates. Underlying data sources are Excel Tables.

Any input would be appreciated.

[V-lianl-msft]

Hi @Anonymous ,

When you mark the table as date table,you lose Power BI's built-in date hierarchy.To get it back you would have to un-Mark as Date Table.The other way is creating the hierarchy manually.Add Year,Quart...columns first.Then 'New hierarchy' for date column.Then add Year,Quart...columns to the hierarchy.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

---

@V-lianl-msft wrote:

Then 'New hierarchy' for date column.

---

How do you do that on a mark-as-date table?

[Anonymous]

@V-lianl-msft yes, that is what I finally figured out after further blog post reading, youtube video and my own experimention. This behavior is not well documented IMHO.

On another note, I'm using 

VAR BaseCalendar = CALENDARAUTO(3)

In my date table, but what I show quarters in a visual, the quarter numbers do not seem to be properly aligned to March/April. I would expect to see for instance 2020.2, 2020.3, 2021.4, 2021.1, 2021.2

Instead it appears quarter numbers are still aligned to the first of the year. Am I missing something?

[V-lianl-msft]

Hi @Anonymous ,

Could you take a screenshot to let us know more about your scenario?

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @Anonymous 

check auto date/time paremeter in Options (data load sections)

also, you can read here https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time

[Anonymous]

@az38 Date/Time is enabled for both the App and the File... any other suggestions...? Read the article, didn't offer much. I am using the Enhacned Dataset Metadata preview mode, but MSFT's documentation doesn't say anything about that impacting Time Inteligence or Date Functions.

[Anonymous]

It appears this may be an effect of marking a date table as a date table, not well documented in my opinion. It looks like going the route of a Marked Date table requires you to build your own date hierarchy in the date table. I'm not clear why it wouldn't auto-generate a date hierarchy in the Date Table, seems like this would make sense, particularly if the Date table is being generated in PowerBI with the Calendar function.