Sorting month order in line chart

[Dunner2020]

Hi there,

I have a date table that contains the date from January 2019 to March 2025. The structure of the table looks like following:

I created the date column using following code: 

let
Source = (StartDate as date, EndDate as date, RYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, "Year Number", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "Quarter Of Year", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "Month Of Year", each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, "Day Of Month", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [Month Of Year] * 100 + [Day Of Month]),
InsertMonthName = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "Month In Calendar", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter In Calendar", each "Q" & Number.ToText([Quarter Of Year]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Day In Week", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "Day Of Week Name", each Date.ToText([Date], "dddd"), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "Week Ending", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"Month In Year", each [Year] * 10000 + [Month Of Year] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [Quarter Of Year] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"Month In Year", Int64.Type}, {"DateInt", Int64.Type}, {"Day Of Month", Int64.Type}, {"Month Of Year", Int64.Type}, {"Quarter Of Year", Int64.Type}, {"Month In Calendar", type text}, {"Quarter In Calendar", type text}, {"Day In Week", Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, "Short Year", each Text.End(Text.From([Year]), 2), type text),
AddRY = Table.AddColumn(InsertShortYear, "RY", each "RY"&(if [Month Of Year]>=RYStartMonth then Text.From(Number.From([Short Year])+1) else [Short Year]))
in
AddRY
in
Source

I tried to create a line chart in which x-axis contains the Month (starting from April 2020 and ends on March 2021) as shown in the figure:

I want to arrange Month name w.r.t to order i.e. starts with April 2020 and ends at March 2021. I tried to sort the date column with Month name (column only contains the name of the month) in ascending column but did not get the desired result. Could anyone help me in fixing the issue?

[FrankAT]

Hi @Dunner2020 

you have to have a column with month number. Sort the month name by this column.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Dunner2020]

Hi @FrankAT ,

I created the Month number column (starting from April) and sorted out but no luck:

After the sort still looks the same

[amitchandak]

@Dunner2020 , if it is month

Months Like Jan /Feb or January

Then you should mark the Month number as the sort column

If the month is like MMM-YYYY or MMMM-YYYY

then you need have sort column like

Month Year Sort= format([Date],"YYYYMM")

and mark this as sort column. refer steps at https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[Dunner2020]

@amitchandak , I created a new sort column as you mentioned in the reply. It sort out the values in data view as shown in the picture:

However, it did not sort out the order of Month in line chart as shown in the picture:

[v-lili6-msft · Accepted solution]

hi  @Dunner2020 

In edit queries, add a costom column as below:

if [Month Of Year]>=4 then ([Year]*100)+[Month Of Year]-3 else ([Year]-1)*100+[Month Of Year]+9

Now use this custom column to sort by column

Result:

and here is sample pbix file, please try it.

Regards,

Lin