Adding week to date hierarchy

[Anonymous]

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

[Anonymous]

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]

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]

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

[Greg_Deckler · Accepted 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.

[Anonymous]

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]

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

[samnay]

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]

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