Forum Discussion
Relative Date - Axis Labels?
I have a simple set of tables with stacked column chart visuals. Now that I have more than 1 year of monthly data, I'm attempting to change the visual to show the most recent 12 months. As an example, the original visual looked like this.
My problem is that January was taking January 2021 and January 2022 data and adding them together (that's why the value is higher and blue). What I want is for January of 2022 to show up AFTER December (essentially always showing the most recent 12 months). I changed the filter on this visual from the Date/Month field in the relevant table with thie data to just the entire Date field, which then gave me the option to choose "Relative Data" and set that to show the previous 13 months (including today), great (so I thought)! When I did that, the visual did change, but not quite right.
It looks like that changes fixed the fact that it was adding January 2021 and 2022 togther and only showing January 2022 (the reason there is no line because the January 2022 data was at 60%, lower than the 80-100% range I currently have set, no big deal I can update that), however for the most recent 12 months I would always expect that it would add subsequent months (e.g., January of 2022) at the end, which it did not. I then changed the Axis, which was set for the Date/Month from Date Hierarchy to simply Date, and this updated the visual to what I would expect (again, no bar for January 2022 because of its currently value however at least now it's to the right after December of 2021).
The issue now and long winded way to get to my question is, why are the labels only showing Jul 2021 and Jan 2022? I tried a few things to fix this to no avail. Ideally this displays the month and year like it's doing, but for all of them (perhaps I need a vertical setting or something). Any help would be appreciated, thanks!
Hi, jmscrx
You have two choice:
- Manually drag the date column into the visual filter pane, and then you can also set the relative date in filter pane, and the data will still display the corresponding results.
- Using custom format function in model view, set date format 'MMM YY', then you can get dates like 'Jan 21', This won't change your data type.
Like this:
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.Best Regards,
Community Support Team _Janey
6 Replies
Try changing your X axis from continuous to categorical:
It displays that way when set to continuous so it can adjust based on the number of data points there are in the axis. If you were showing data for 10 year it would only have the year value on the label for example.
Thanks for the reply jdbuchanan71 . I actually had stumbled upon and tried that, though the display gets a bit messy so I changed the format of the Date column in the table within PowerBI to mmmm-yyyy, and now it looks better.
Ideally I just want the abbreviated Month and year (e.g., Jan-2022, Feb-2022), is that a custom format or someting?
The axis follows the formatting of the field so you could change the format to something like mmm-yy to get the shorter string.
Take a look at this article about how to create a chart friendly mmm yy field that is still a date field. When you make one with FORMAT that is a text field.
https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/
