Cannot create a date Hierarchy

[Chema_Ortega]

Hello everybody! I hope all of you are Ok!

I am trying to create a date Hierarchy, but the program does not allow me to do it, I don't know why! (I mean in the Desktop, not in the Service or Power Query). I have created the Hierarchy, but when I try tro drag and drop another field, there a ⛔ sign pops up!

Does anyone know how to solve this? Thanks a lot!!! Best regards,

Chema

[v-yangliu-msft · Accepted solution]

Hi  @Chema_Ortega  ,

1. If you are using direct query then automatic date hierarchy is a limitation as is time intelligence. I would suggest to manually create a hierarchy. Right click date and choose new hierarchy and all the members you need to it (Year, Quarter, Month etc.).

2. In that file's options, under Data Load, is "Auto Date/Time" unchecked? That will disable automatic hierarchies.

File -> Options and settings -> Options -> Data Load -> and select the Time intelligence Auto Date/Time check box.

3. If the data type of the  column is date/time, it will never match a date in your date table even if the format is date. There are more digits there that you've simply hidden with the formatting. The data types should match.

This is a link to related content, I hope it will help you:

https://community.powerbi.com/t5/Desktop/Date-hierarchy-missing/m-p/678089

https://community.powerbi.com/t5/Desktop/Date-Hierarchy-Doesn-t-show/m-p/525460

https://community.powerbi.com/t5/Desktop/Can-t-create-a-date-hierarchy/m-p/854972

Best Regards,

Liu Yang

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

[Anonymous]

Does it happen with all the fields or only with CalendarWeek? Maybe this happens because weeks do not go evenly into years? Try with other fields and tell us what happens.

[Chema_Ortega]

Hi Daxer,

Thanks a lot. It happens with all the fields in the table...