Forum Discussion

Dunner2020's avatar
Dunner2020
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Sorting month order in line chart

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?

5 Replies

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion

    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's avatar
      Dunner2020
      Icon for Post Prodigy rankPost Prodigy

      Hi FrankAT ,

       

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

       

       

      After the sort still looks the same