Forum Discussion
Line chart
I am using a line chart visual:
x axis - months
y axis - number of tickets
I would expect my line chart display months in a chronological order Jan Feb Mar, etc.,however, the first month on my x axis is January 2018 and then April 2017,May 2017, June 2017,etc.
I basically need to take the most current month and place it all the way to the right. The selling tickets started in April 2017 so April month should be to the far left.
Also, is there a way to add a year to x axis so that it reads Month/Year?
Anyone had similarir issue?
Thank you.
1. add Month and Year columns to your table, reference to [Date] column:
Month = Sales[Date].[MonthNo]
Month Name = Sales[Date].[Month]
Year = Sales[Date].[Year]
2. sort the 'Month Name' column by 'Month'
3. use the double arrow to expand the graph to Months
5 Replies
Currently, to do this on a line chart, you need to create a new column that is a concatenated Year-Month. If it's formatted as text, you'll likely need to include a Month Serial ID column (as I like to call it). This column is an integer, but it doesn't go from 1-12 like your typical month column. If your calendar starts with January 2016, then January 2017 would show 13. This column is used to properly sort the Year-Month text column.
Copy and paste these 2 lines of code to the Advanced Editor:
AddYearMonth = Table.AddColumn(<the previous step name>, "Year Month", each [Year] & " " & [Month], type text), AddMonthSerialID = Table.AddColumn(AddYearMonth, "MonthSerialID", each ([Year] - List.Min(AddYearMonth[Year]) ) * 12 + Date.Month([Date]), Int64.Type) in AddMonthSerialID
This code assumes you have 3 columns in your table, [Date] as date, [Month] as text, and [Year] as number.
[Year Month] is just simple concatenation.
[MonthSerialID] subtracts the current row's year from the smallest year (that's what the List.Min() function does), multiplies that result by 12, and then adds the month number (an integer 1-12) to that for the final result.
If you have a [Month Number] column already, you can use that instead. Just replace the Date.Month([Date]) with [Month Number].
Hope this helps!
similarly, but without using Advanced Query. Add 2 columns to your table: Month and Year (both numerical).
In visualisation, place Year and Month in Axis. Jan-2018 is shown as last:
