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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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?

3 REPLIES 3
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.