No Date Hierarchy with Date Column

[mregnide]

Hello,

I've having difficulties with a column not having a date hierarchy. The data set is being pulled into Power BI from a SQL stored procedure. The part of the select statement that generates that column is as follows

CONVERT(varchar,dateadd(week,datediff(week,0,CDCLSVC.BEG_DATE),0),23) as Week_of

When I go into Edit Queries -> Transform tab, I can confirm that that column has Data Type: Date.

When I go into the Data view -> Modeling tab, I can confirm that the column also has Data type: Date and format MM/dd/yyyy.

When I go into Model view, I can confirm that this data table isn't linked to another table based on this column.

If I go into the report view and click the Week_of column and attempt to add a new hierarchy, a new hierarchy is added named Week_of Hierarchy, but the only element of the hierarchy is Week_of. It does not automatically create a date hierarchy which is what has happened for me in the past when I've had this problem.

I'm not realy sure what else I can do to make a date hierarchy out of this column. Any help would be appreciated.

[v-easonf-msft]

Hi, @mregnide 

Please check whether you disable  the function of  Time intelligence  automatic hierarchies.

You have to enable the the function of "Auto date/time for new files" ,then  create a new file to pulled  the dataset into Power BI .

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mregnide]

@v-easonf-msft 

I can confirm that the auto date/time for new files option is selected.

I don't know if it matters, but this is a data source that already existed and I added the Week_of column in the stored procedure.  There are other date columns used in this data set that do have date hierarchies.

[v-easonf-msft · Accepted solution]

Hi , @mregnide 

Don't know the specific problem cause.It is suggest to create a support ticket to investigate the issue. 

https://powerbi.microsoft.com/en-us/support/ 

And here is a  workaround :

You can  create four calculated columns to extract the  "Year," "Quarter," "Month“,” Day " from column"Date".

Then you can manually create new Heirarchy, by right-clicking on the date, then drag in the other pieces you need ("Year," "Quarter," "Month“,” Day, ").

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.