Forum Discussion

jmscrx's avatar
jmscrx
Helper I
4 years ago
Solved

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...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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

jdbuchanan71's avatar
jdbuchanan71
Super User
4 years ago

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's avatar
    jmscrx
    Helper I
    4 years ago

    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's avatar
      jdbuchanan71
      Super User
      4 years ago

      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's avatar
        jmscrx
        Helper I
        4 years ago

        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?