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

[ms007]

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.

[Anonymous]

Hi, 

I've managed to solve this for the data I have in the current month, but have yet to test the formula in the next month to see if it's working properly. 

So I have a calendar table with the following structure:

1st day of month	Year	Month Number	Month Short Name
01-01-2020	2020	01	Jan
...	...	...	...

Then I've added a custom column with the following code:

Month Sort = 
VAR __rowdate = 'Calendar Table'[1st day of month]
VAR __lastdate =
    CALCULATE (
        LASTDATE ( 'Calendar Table'[1st day of month] ),
        ALL ( 'Calendar Table' )
    )
VAR diffY =
    DATEDIFF ( __rowdate, __lastdate, YEAR )
VAR diffM =
    DATEDIFF ( __rowdate, __lastdate, MONTH ) + 1
VAR result =
    IF (
        diffM > 12 
            && MOD ( diffM, 12 ) <> 0, 
        MOD ( diffM, 12 ),
        IF ( 
            diffM > 12 
                && MOD ( diffM, 12 ) = 0, 
            diffM / diffY, 
            diffM 
        )
    )
RETURN
    result

Then you sort Month Short name by this new column.

The idea is to use MOD function to remain in the range 1...12 once the increment for relative month number (diffM) passes 12. 

In your case you would probably change __lastdate to your current slicer selection and then work with n months prior to it.

Maybe not an ideal solution, but my graphs work alright (for the moment).

Let me know if it works for you. 

Cheers,

[vanschex]

Hi,

Was a solution found for this? I have exactly the same problem. I had solved it using a month sort but if the user selects a different begining month, the month name is sorted incorrectly.

cheers

Emil

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[Ashish_Mathur]

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]

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.

[Ashish_Mathur]

Hi,

Perhaps i need to see your data and expected result.