Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 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.
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
Solved! Go to Solution.
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.
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?
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.
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
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.
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
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
75 | |
63 | |
52 | |
47 |
User | Count |
---|---|
218 | |
86 | |
64 | |
63 | |
60 |