Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
GrandWizBiz
New Member

Date Hierarchy

I am struggling to understand the concept of heirarchies and how their interact with other columns, I've watched and read alot of content but clearly am clearly missing something obvious. 

 

I have a table, it includes multiple date columns like "Date Created" and "Date" "Date Closed" and a few others. Each for formated like this: xx/xx/xxxx xx:xx:xx

 

I want to be to have a hierarchy that allows me to do Day (Name)/Week/Month(Name)/Quarter/Year 

 

And then a few combinations such as Month-Year & Quarter-Year. 

 

My column looks like this:

 

GrandWizBiz_0-1675716378222.png

GrandWizBiz_1-1675716414496.png

and is formatted to this: 

GrandWizBiz_2-1675716445373.png

 

 

I've tried building out a full date table from scratch, I can make the individual columns like this:

 

GrandWizBiz_3-1675716550102.png

but when turned into a hierarchy I cannot form a relationship with my 'primary' table. 

 

Anyone able to help?

5 REPLIES 5
jxutj
Frequent Visitor

Documentation on the auto-generated date hierarchy

 

Auto date/time in Power BI Desktop - Power BI | Microsoft Learn

YanaUser04
New Member

Hello, maybe this article will be useful to you for a general understanding of this topic: https://insightsready.com/blog/post/date-hierarchies-in-power-bi-integration-extension-by-insights-r... 

GrandWizBiz
New Member

Hello!

 

Thanks for your reply. It seems like my date columns havent auto generated - which is odd as my legacy file I made a few years ago using the same underlying dataset did. 

 

I have time intelligence enabled:

 

GrandWizBiz_0-1675761173424.png

 

But all my date columns do not have the calander icon aligns to them:

 

GrandWizBiz_1-1675761218350.png

My end outcome is a I want to be able to build visuals that show the number of sales I have aligned to day/week/month/quarter/year and a mixture of those such as month-year. 

 

I'll continue reading through your documents as well though to see if there is something I have missed. 

 

 

Hi , @GrandWizBiz 

Thanks for your quick response!

For "Why there is no date hierarchy", you can refer to this document:
Why there is no date hierarchy - Microsoft Power BI Community

 

And for your need , i recommend you to create  a custom hierarchy in your side.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

v-yueyunzh-msft
Community Support
Community Support

Hi , @GrandWizBiz 

Power Bi Desktop automatically generates us a hierarchy of dates (year, quarter, month, day). If you want to customize the hierarchy, you are right, you need to create your own columns with multiple dimensions, and then you can directly place them in our Matrix Row header or create a custom hierarchy.

For more information, you can refer to :
Power BI Date Hierarchy - Complete tutorial - EnjoySharePoint

And when you create a hierarchy, you need to note that the data of your previous level must be above the scope of your next level.

 

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

 

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.