cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mregnide
Regular Visitor

No Date Hierarchy with Date Column

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.

1 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.

 

View solution in original post

3 REPLIES 3
v-easonf-msft
Community Support
Community Support

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 .

 

01.png

 

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.

@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.

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.

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors