cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

change the format date of the date hierarchy

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

Capture.PNG

mmm/yy would be really nice

 

Thank you and have a nice weekend !

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: change the format date of the date hierarchy

Hi @trungnguyen 

 

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/

 

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

View solution in original post

6 REPLIES 6
Highlighted
Helper II
Helper II

Re: change the format date of the date hierarchy

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) 

 

 

Highlighted
Resolver I
Resolver I

Re: change the format date of the date hierarchy

Have you thought about just doing

 

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

Highlighted
Helper III
Helper III

Re: change the format date of the date hierarchy

Hi @Williamspsouza @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 🙂

 

Highlighted
Community Support
Community Support

Re: change the format date of the date hierarchy

Hi @trungnguyen 

 

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/

 

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

View solution in original post

Highlighted
Super User II
Super User II

Re: change the format date of the date hierarchy

Hi @trungnguyen,

 

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
New Member

Re: change the format date of the date hierarchy

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors