Last 12 Months Sort Order- Matrix Visual

[aflintdepm]

I am trying to set up a matrix visual to show data over the trailing 12 months.  I have a calendar table and have added a sorting column created by 

MonthSortOrder = FORMAT([Date],"YYYYMM") 

However, when I try to use this to sort my Month column, I get this error

What is the best way to create a dynamic sort, in a matrix, that will show the trailing 12 months (or other period) with the most current month as the end?

[danextian · Accepted solution]

Hi @aflintdepm 

It appears you are trying  to apply a custom sort to a column that contains just the month name thus the month get sorted more than once unsuccessfuly due to it having multiple years (eg, you cannot apply yyyymm sorting to a month only has mmm or mmmm but you can to mmm-yy). But custom sorting will not give you the trailing 12 months. It is only for sorting.  I have attached a DAX calendar with running month numbers in the rightmost columns referencing the earliest or latest month in the table.