Power BI Desktop - Date hirarchy does not work properly

[SIH007_1]

Hi, I imported an Excel table into my data model in Power BI. In M the date field has the data type date. When I check my fields in Power BI after refreshing the data, the date field seems to be unknown. Normally it should offer using a hierarchy automatically, but it does not, if I create it manually, it does not work either (see screenshot) Apart from that there is not the usual calendar symbol which normally helps to find date fields in the field area. Any idea how to solve this issue?

[Anonymous]

Hi @SIH007_1 ,

When the conditions below are met, the date hierarchy will work for you.

• Enable automatic date and time in Power BI Desktop.
• 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

For more information，please refer to Why there is no date hierarchy  

Best Regards,
Community Support Team _ Caitlyn

.

[SIH007_1]

The solution was that I had not realized that the datatype was still set to any, after setting it to date everything works fine