cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ms007 Frequent Visitor
Frequent Visitor

Sort chart for Trailing 12 months (or Rolling 12 months) by month

I want to display a bar chart with Trailing 12 months (TTM) using a Relative Date slicer with Last 12 months. The issue is that my last 12 months don't sort correctly. If we're in Sept 2018, I'd like the the first bar to be Oct 2017, then Nov 2017, and go all the way to Sept 2018. But it doesn't happen like that as the bars sort alphabetically or by month number (I have a column month number also).

I also tried to Sort by 'Month number overall' which I also have in my table. But it throws an error because my Month Names have more than one Month Number Overall. That is, Jan is 171, and also 183, and 195 etc.

 

I'm looking for an easy solution using the Relative Date Slicer. Please advise.

3 REPLIES 3
Super User
Super User

Re: Sort chart for Trailing 12 months (or Rolling 12 months) by month

Hi,

 

This is what you should be doing

 

  • Create a simple 2 column table (Months_order) with Month Names in column 1 and Order in column 2.  Order will be numbers from 1 to 12
  • Create a Calendar Table with Months extracted from all dates by using =FORMAT(Calendar[Date],"mmmm")
  • Create a relatiosnhip from the Months column of the Calendar Table to the Months column of the Table created in step 1 above
  • In the Calendar Table, enter =RELATED(Month_Order[Order]) and rename this column as Order
  • Click on any one cell in the Month column of the Calendar Table and go to Home > Sort by column > Order
  • Create a relationship from the Date column of the base data table to the Date column of the Calendar Table
  • In your visual drag the Month from the Calendar Table

Hope this helps.

ms007 Frequent Visitor
Frequent Visitor

Re: Sort chart for Trailing 12 months (or Rolling 12 months) by month

Hi Ashish,

 

Thanks for your response. Unformtunately, this does not produce the desired results. This will sort the months as Jan, Feb, Mar........., Nov, Dec. 

I wish to sort them based on last 12 months from the current month. So if we're in Sept 2018, I would like the chart to display 12 bars, starting left to right from Sept 2017, Oct 2017, Nov, 2017..........Jun 2018, Jul 2018, Aug 2018. Thanks.

Super User
Super User

Re: Sort chart for Trailing 12 months (or Rolling 12 months) by month

Hi,

 

Perhaps i need to see your data and expected result.