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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Adding week to date hierarchy

A PBI amateur here!

 

I have a date field and when I use it as axis, Power BI automatically creates a hierarchy that has the Year, Quarter, Month and Day. I want to add the week number between Month and Day. So, instead of going with the built-in date hierarchy, I decided to stack up individual columns like this:

 

date hierarchydate hierarchy

While this gives me the desired result, It brings in some redundancy. When I drill down beyond the month leve, the year gets added again. please check the image of my x-axis.

Problem with axis.PNG

 

See how it says 2018 Qtr 2 April 2018?

Is there any way to change this, or add week number to the built-in date hierarchy? Your help is much appreciated.

 

Thanks,

Bharath

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

You could try turning off concatentation in your x-axis. To add week hierarchy you would want to create a separate date table using CALENDARAUTO and then add a column using WEEKNUM to that table. You would also want to add columns using YEAR, FORMAT([Date],"mmmm" and a quarter function probably using SWITCH. Then you could build a hierarchy manually using these columns. Relate that table to your other table in the obvious way.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

The name of this topic is wrong. It should be "formatting week column in date hierarchy". I am looking how to add week to the date hierarchy, could you please share how did you do it?

Anonymous
Not applicable

I was able to fix this by creating custom columns for week number and formatting it in the way I want it to be.  week_number actually included the year, which goes against our variable naming criteria and I was thrown off by that.

 

Thanks again for helping me out with this.

v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

What do the data of "week_number" and "day_of_month" look like? Can you share the file? Maybe only the "Date" table is needed.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

You could try turning off concatentation in your x-axis. To add week hierarchy you would want to create a separate date table using CALENDARAUTO and then add a column using WEEKNUM to that table. You would also want to add columns using YEAR, FORMAT([Date],"mmmm" and a quarter function probably using SWITCH. Then you could build a hierarchy manually using these columns. Relate that table to your other table in the obvious way.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

I did everything but as mentioned by others, what is the correct way to relate this table with the others? I tried  connecting the custom table with a 1 to many relation to the other target date column in the target table but it doesn't work

Anonymous
Not applicable

@Greg_Deckler What is the 'obvious way' of relating the table. I'm glad for you that everything is so crystal clear.

Hello, 

Thanks for this reply , it helps.
I would like to know if there is any way to calculate the number of week but depending of the month.
For more details, in a month i have 4 weeks ,so every time i would like to now data related to the firt week of juanary , the second one, thirth one fourth and then first week of february ....

Thanks in advance,
Sara

Anonymous
Not applicable

@samnay I know that this is a very late reply but your question has been answered here

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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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