report dates not showing in order

[Caitlin_Knox]

I've created a date table, but something is wrong. On my reports, the dates do not show in chronological order. I currently have the date table column that holds the actual dates to sort ascending. From that column I parsed out the short year and month name and then merged those two columns together since the ultimate goal is to show a trailing twelve months visual- so one month will have two entries[one for the year prior]. I also tried creating a calculated column to merge the short year and month name with the same results as shown below. Its like they almost go in order, then get crazy. The earliest month in the data set is May 2015, and the most current is May 2016. So in this visualization, all data should show.

[Anonymous]

You need to define what the column should sort by.  Create an index column with a unique value in descending order for each distinct Calendar MonthName in your case, then select the CalendarMonthName Column and choose "Sort By" and select your new index column. 

[Caitlin_Knox]

I have a column 'MonthID' that I've set to descending order. I then chose 'Year-Month' to sort by 'MonthID' but am still seeing the same behavior, although slightly better

[Anonymous]

I'm making a couple guesses here, but it looks like your [MonthId] column is a concat of [MonthSort] and [Year].  You need to reverse the order of the concat.  You want it to be year first, then month.  So MonthID in your calendar is returning 1215 for December 2015, but it should be 1512.  That way January 2016 is 1601, rather then 0116. 

[Sean]

@Caitlin_Knox create a column like this... and use it in the chart

Year-Month = FORMAT('Calendar'[Date], "YYYY-MMMM")

EDIT: You can of course use only month abbreviations too...

Year-Month = FORMAT('Calendar'[Date], "YYYY-MMM")

EDIT 2: It actually works reveresed too...

Year-Month = FORMAT('Calendar'[Date], "MMM-YYYY")

[Caitlin_Knox]

@Sean Hi, I'm still having the same issue. I think its probably a problem in my date table on the way I have it sorted there?

[Sean]

@Caitlin_Knox Create this column (to be used to sort only)

Sort Order = FORMAT('Calendar'[Date], "YYYY-MM")

Then create another one (this will be used in visualizations)

Year-Month = FORMAT('Calendar'[Date], "MMM-YYYY")

Then go to Modeling Tab => select Year-Month column => click Sort By Column => select the newly created Sort Order column

This should fix it! Let me know...

[Caitlin_Knox]

@SeanSame issue...

[Vvelarde]

@Caitlin_Knox the sort action is in the year-month column..and select "Sort by Column"--Sort Order.

[Vvelarde · Accepted solution]

@Caitlin_Knox

The problem is the column Month ID;

Replace by this other column "MonthID-Correct"

[mwebb77]

@Vvelarde I had the same problem. The only way I could get it to work was to create a column YYYYMM (the data is sorted in the underlying table on that column). It had to be formatted as a whole number, it then sorts correctly. 

If I leave the table sorted on YYYYMM but add a new column MM-YYYY (text format) it goes out of order again. How do I display this data so that the MM-YYYY shows on the x-axis but in month/year order (see screenshots)?

[Caitlin_Knox]

Thank you everyone for your contribution and help. It's been one of those days! When I adjusted the MonthID per your suggestions, THEN sorted the Year-Month column by the MonthID- its finally working as expected. Lesson learned 🙂

[Anonymous]

Are you sorting the month-year column by the sort order column?  The screenshot looks a little confusing.