Sort chart months up until current date

[Anonymous]

Hi,

I want to show some data which has sales amount from the previous 12 months on a column chart. When I add all my data to the chart it automatically sorts the columns from January-December even though September-Decembers data is from the year previous.

below is how the data is set out.

Thanks,

[TomMartens · Accepted solution]

Hey,

you have to use a column for the x-axis that also contains the year.

You can create a Calculated Column using the DAX statement: 

Year-Month = FORMAT('<tablename>'[MonthDate] , "YYYY-MM")

This will create something like this "2018-09" and will represent September 2018. You might want to create two columns one containning the year and one containing the month name (please be aware that the month name has to be sorted by a month number: https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column#sort-using-the-sort-by-column-butto...)

To create a chart like this:

it's necessary to create two additional columns that contain the year and the month. To create a calculated column that contains the monthname, you can use this DAX statement:

Month Name = FORMAT('<tablename>'[Monthdate] , "MMM")

Make sure that this column is sorted by an extra column that just contain the month number, you can create this calculated column by using this DAX:

Month Number = MONTH('<tablename>'[MonthDate])

After creating all the columns it's necessary to arrange the year and month name column for the visual like so:

Make both colummns are expanded:

Disable the property "Concatenate labels" from the X Axis band of the visual:

Make sure the X Axis is sorted accordingly:

You may also have to change the sort order to "Ascending".

Hopefully this provides you with some ideas.

Regards,

Tom

[Anonymous]

That's perfect! Thank you ever so much.