Relative Date - Axis Labels?

[jmscrx]

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!

[jdbuchanan71]

@jmscrx 

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/

[jdbuchanan71]

@jmscrx 

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.

[jmscrx]

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?

[jdbuchanan71]

The axis follows the formatting of the field so you could change the format to something like mmm-yy to get the shorter string.

[jmscrx]

@jdbuchanan71 - Thanks again. I've created a custom colum in the table.

This formatted the date in that column how I want it however when I use this Custom_Date field in my filter on the visual, I lose the Relative Date option, which is crucial to always showing the prior 13 months. I went back to the modeling tab and realized that the Data type for this new column was set to Text, so I changed it to Date, however when I do that I lose the format that the above DAX formula set the new column to and can only seem to use the preset formats again from the Format drop down.

Is there a way to keep the DAX formatting but maintain the ability to use Relative Date for this field, or set the data type to Date and have it still obey the DAX?

[v-janeyg-msft · Accepted solution]

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