change the format date of the date hierarchy

[Anonymous]

Hi everyone,

I would like to change the format of the date.

In fact, I used the date  hierarchy with only Year and Month. After,  I drill down a level of the hierarchy to display the measure by month/year.

The display is not nice to see and I would like to change the format. How can I do it ? I have tried to change the format in the Edit Query or in the Schema part with the custom format but it remains the same thing

It's the current result

mmm/yy would be really nice

Thank you and have a nice weekend !

[danextian]

Hi @Anonymous,

You may create a custom data hierarchy using FORMAT function for each level in the hierarchy.  You may also disable the concatenate option so the year on the most bottom part of the Y axis with the month name on top. 

Please share the specific format you have in mind. It might be doable.

[Jorgast]

Have you thought about just doing

Column Name = Format(Date, "MMM - YYYY") This will eliminate the drill-down feature.

[Anonymous]

Hi @Anonymous @Jorgast 

Currently, I'm using the formula FORMAT with mmm/yy for my date column.

Your solution is good but it works only when you don't need to do an agregation. I have to count lines for each month that's why I must use the date hierarchy and in this case the FORMAT with mmm/yy doesn't work anymore

You can correct me if it is wrong 🙂

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Based on my research, I am afraid you could not change the format of the date hierarchy, I suggest you to create custome data hierarchy. You could refer to below link to create multiple date columns,  and you can achieve hierarchy function by adding multiple columns(Year, Month)  to the "Axis" of a bar or column chart. This way, the chart will also enable you to dill down the data.

Reference:https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

[JKH66]

I have just selected the name of the Date hierarchy in the Fields panel and typed the format in the Format box, rather than trying to select from the drop down, on the Home ribbon.

Power BI has happily saved this.

[Anonymous]

You can create a custom date format from the Model view, but not the Date view.

[Anonymous]

You can create a new column and use it: =FORMAT([DATE];"MMM/YY")

then, use the field date o sort this column (Select the new column, click on "Modeling", click on "Sort by Column" and select the field Date) 

[miguins]

Hi, the problem I have with this calculated column is that the order is alphabetical, not by # of month as it happens with Data Hierarchy