Wrong months order on graph

[CloudMonkey]

Hi,

Please can you tell me how to put these months in order? (November and December relate to the previous year so should be on the left hand side of the graph):

And these are the settings:

I was expecting to be able to sort by date, but it insists on sorting only by "Month Day":

Thanks for any help,

CM

[v-zhenbw-msft · Accepted solution]

Hi @CloudMonkey ,

We can use the following steps to meet your requirement.

1. We need to create a month number column in your table, then create a calculated table contains sort value.

Month = Month('Table'[date])

Table 2 = ADDCOLUMNS(SUMMARIZE('Table','Table'[Month name],'Table'[Month]),"sort value",MOD([Month]+13,12)) 

2. Then create a relationship between month table and Table based on month name.

3. Create a line chart visual, put the Table 2[Month] to Axis, and sort by [sort value].

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

You need to add a Month Number field to your date table, and then order the Month Name column by the month number.

Here is a very simple date table...

Date = 
VAR Dates = CALENDARAUTO(12)
Return
    ADDCOLUMNS(Dates,
    "Year", YEAR([Date]),
    "Quarter", FORMAT([Date], "\QQ"),
    "Month Num", Month([Date]),
    "Month", FORMAT([Date], "mmm"),
    "Week Num", WEEKNUM([Date],1),
    "Day of Month", DAY([Date]),
    "Day of Week", WEEKDAY([Date],1),
    "Day", FORMAT([Date],"ddd")
        )

 You can assign the order by either from the Model view, in the properties pane, under advanced, select the "Month Num" as the Sort by column. OR you can do it from the Report view, by selcting your Month column, and then in the toolbar under Column tools, selcet the Sort by column.

Hope that helps!

Kim

[CloudMonkey]

I thought you meant each month should increase by 1 e.g.

November 2019 = Month 1

December 2019 = Month 2

January 2020 = Month 3

February 2020 = Month 4

March 2020 = Month 5

But looking at the code I think it means:

November 2019 = Month 11

December 2019 = Month 12

January 2020 = Month 1

February 2020 = Month 2

March 2020 = Month 3

Please can you confirm? But if it's the latter I don't understand how that would solve my problem?

Thanks,

CM

[CloudMonkey]

LOL no worries. If I add year that works (if I switch to categorical data instead of continuous). But my users don't want to see the year on the axis - please do you know how to make this work without the year being visible?

[Anonymous]

shoot... my previous reply doesn't solve your problem!

To solve this, you'll need to have the Year included in your table.