Month not getting sorted Correctly

[abhishek_300]

Hi ,

When I am creating a dasboard based on Month and Sales. The Sales are not getting sorted correctly by Month.

The month are appearing like below.

January -2016

January -2017

February-2016

March-2016

April-2016.

How to fix this so that the January month 2017 appers after the December month 2016.

I am connecting to the tabular model which has this data and I am using Power BI Pro.

Please advise.

[tringuyenminh92]

Hi @abhishek_300,

You need one OrderColumn in your tabular model with value = Year*100 + MonthNo (201607, 201608, ..) so you could sort column Month Year by that order column in Power BI.

Are you using Import mode or direct query? Cause using import mode, you could create order column by adding Condition Column in Power BI, but with Direct Query, you need create order column in server side of tabular model

[abhishek_300]

hI,

Thanks for your reply.I am using direct query and I am creating dataset directly from the Power BI Pro. I am not using Power BI desktop.

So do I need to create an Order column on tabular model using below query

OrderColumn = Year*100+Month_Number.

So that will be 2017*100+1= 201701.

So does that mean I will have to show to the users 201701 and I cannot use the column with the value like January-2017 in the visual.

[tringuyenminh92]

Hi @abhishek_300,

You show January-2017 column but sort by order column, you need to do it in Power BI Desktop and publish pbix file.

[anandav]

Hi @tringuyenminh92,

I have created a calendar table in Power BI and I can sort by month. My data start from Jun 2016 to March 2017. The problem comes when the year increases – from 2016 to 2017. Then the date is displayed as 2017 months first and then 2016 months. i.e. Jan, Feb, Mar, Jun, Jul, Aug…….

I tried creating a column YYYYMM and tried to index the month name using that column in DAX. But I get an error “.. You cannot sort the Month Name column by YearMonthIndex. You cannot have more than one value in YearMonthIndex for the same value in Month Name.”

How can I sort the months so that the roll over of year will not cause a problem?

Thanks in advance.

[tringuyenminh92]

Hi @anandav,

Could i have your pbix file for observation? so I could quickly figure out solution for your sorting expectation.

[anandav]

Hi @tringuyenminh92,

I solved the problkem using @v-haibl-msft's suggestion here.

It's the same solution like you have suggested but it uses a column 'Month YYYY' column and sort that using the YearMonth column. That worked.

But I still couldn't figure out why I get the error when I try to sort the Month Name using the YearMonthIndex field,

[anandav]

Hi @tringuyenminh92,

I have a similar problem with sorting weeks explained here.

Will you be able to assist with the issue?

Thanks in advance.

[abhishek_300]

This filed is disabled has I have connected to the tabular model using direct query.

[tringuyenminh92 · Accepted solution]

Hi @abhishek_300,

For Direct Query of tabular, It's so strange cause if you have Dates(Date column, Month no column, Month Name column,...) table and Fact table in your tabular with relationship of Date column, it will be sorted automatically when you use Month Name column in chart.

Could you please check your Tabular Model, properties of Month Name column, choose Sort By Column property and assign the Order Column for it?

https://msdn.microsoft.com/en-us/library/hh479566.aspx  (Sort By Column)

Refer topic: https://www.simple-talk.com/sql/bi/creating-a-date-dimension-in-a-tabular-model/

or topic https://www.mssqltips.com/sqlservertip/3228/change-sort-options-on-dimension-attributes-in-sql-serve...

[abhishek_300]

Thanks  tringuyenminh92,I change the sorting order in the properties of Tabular Model and now it is getting sorted correctly.

Thanks for your help