Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 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

Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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.

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

    • samnay's avatar
      samnay
      Frequent Visitor
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
    Anonymous
    Not applicable
    4 years ago

    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?

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.