Line chart

[lukaspowerbi]

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.

[Anonymous]

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!

[pawel1]

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:

[Anonymous]

Nice, I forgot that Line charts now have drilldown!

It's not a bad idea to add that MonthSerialID column anyway though.  It's very handy for doing calculations for trailing 12 months.  That way you can grab the most recent 12 months, regardless of which year they're in.

[lukaspowerbi]

So it seems that adding 2 columns to my table would be easier, would you give me an example with my case?

Not very fammiliar with DAX language yet.

[pawel1 · Accepted solution]

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