Date Format for creating a hierarchy

[kccarbery]

I'm having some issues with power bi recognising my date in a table. When I click on the column it says it's in date format. I've tried extracting the Year, Month and Day, and there doesn't seem to be any irregularities with the column - it switches easily being other date/time formats too.

However, when I see the column in the Data tab, beside the Visualisations tab, I can see its not recognised as a Date format. 

This is causing me a lot of issues as now when I create a date hierarchy with the column, it doesn't recognise the year month or day.

Thanks in advance

[vicky_]

Check the relationships tab - if you've created a relationship between that date column and another date table, then the heirarchy should be available in the date table, and you can use that in your visuals instead.

[kccarbery]

this didn't work unfortunately. I've created a date table and created a relationship between the date column and the date table and unfortunately neither of them are detecting a hierarchy

[Anonymous · Accepted solution]

Hi @kccarbery ,

This is by design. 

Creating a hierarchy in Power BI is simple. You can right-click on the field you want to set as level 1 of the hierarchy in the fields list, and then select Create Hierarchy. After that, you will see a new hierarchy created named your field name “Category” plus the word “Hierarchy”. This would have a hierarchy icon beside it and also an option to expand to the fields of the hierarchy.

To add another level to the hierarchy, you can either simply just drag subcategory to the Category Hierarchy header or right-click on the subcategory and use Add to hierarchy. This adds that field as the second level of the hierarchy.

You can also enable Auto Date/Time to automatically create hierarchies.
Auto date/time in Power BI Desktop

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum