Is there some way to force date hierarchies on a date column?

[olimilo]

I have these 2 date columns, DateA and DateB:

• Neither one is related to a date/calendar table
• Neither one of them can have a relationship to a date/calendar table
  
• Out of 26k rows, DateA has 7 blank rows, while DateB only has 100+ blank rows
• Both of them were set to Date type in Power Query and under the column tools tab
• I have Auto Date/Time enabled for both global and current file settings

DateA set to Date data typeDateB set to Date data type

My question is, how come DateB does not have a date hierarchy whereas DateA has it? Both columns were set to date data type in the query level, and is also set to date data type in the data model. And if possible, how can I apply a date hierarchy to DateB without relating it to a date/calendar table and without making calculated columns?

[v-xulin-mstf]

Hi @olimilo

What is the datasource of DateB?

If you are connecting to a data source through DQ to get DateB, the date hierarchy is not supported, you can only create the hierarchy manually.

Best Regards,

Link

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

[olimilo]

Both DateA and DateB are from the same data source, an Excel file hosted on Sharepoint.

[v-xulin-mstf]

Hi @olimilo,

It works well in my environment.

Please check datatype in both sharepoints and desktop.

Then you can try to reconnect to sharepoints site and check the issue.

Please check this option has been chosed.

In Power BI, by default, whenever a date field is added to a visual, a date hierarchy is automatically created and becomes available for drilldown.

Best Regards,

Link