Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !
Solved! Go to 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/
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.
Proud to be a Super User!
Have you thought about just doing
Column Name = Format(Date, "MMM - YYYY") This will eliminate the drill-down feature.
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 🙂
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/
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.
You can create a custom date format from the Model view, but not the Date view.
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)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |