Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dunner2020
Post Prodigy
Post Prodigy

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:

 

myasir_0-1600205255895.png

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:

myasir_1-1600205387244.png

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?

1 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

4.JPG

Now use this custom column to sort by column

5.JPG

Result:

6.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

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

View solution in original post

5 REPLIES 5
FrankAT
Community Champion
Community Champion

Hi @Dunner2020 

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

 

15-09-_2020_23-38-15.png

 

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

Hi @FrankAT ,

 

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

 

myasir_0-1600207111787.png

 

After the sort still looks the same

 

myasir_1-1600207153891.png

 

@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

 

Sort Month Year.png

@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:

 

myasir_0-1600220976424.png

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

 

myasir_1-1600221037131.png

 

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

4.JPG

Now use this custom column to sort by column

5.JPG

Result:

6.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.