How to sort Month in Dynamic Change Axis ? Sorting Month name

[mdaamirkhan]

Hello All,

I need urgent help on How to sort Month in Dynamic Change Axis ? Sorting by Month name like(Jan 2017 , Feb 2017 and so on). I have share file pbix link: https://drive.google.com/open?id=0BxreilNC4Avsb0dVS3pqQU9HODA  .If you can help on this on my pbix and share with me it will be very helpful.

cannot sort Month

Thanks 

Aamir

[Anonymous]

This idea of having to create new columns to sort is a cluge at best. I have PBIRS and I have no option to create new columns. As well the version of PowerBI is not honoring the Cube's sort. Can you  all fix the underlying problem of not converting everything to strings and keep the data type, sort the data type appropriately...so no more hacks.

That would be helpful.

[mdaamirkhan]

How will I sort the AxisValue column it has (QTR and Month value) in DimTable? Screenshot belowI need to sort this column AxisValue

[mdaamirkhan]

Any update it's bit urgent.

[v-qiuyu-msft]

Hi @mdaamirkhan,

As the Month Period column is a TEXT type column, it will be sorted based on the first character of Month Period values automatically in a matrix visual. 

In your scenario, to work around the issue, you can create a calculated column below: 

DateNew = SWITCH(LEFT(DimTable[Month Period],3),"Jan",DATE( RIGHT(DimTable[Month Period],4),1,1),"Feb",Date( RIGHT(DimTable[Month Period],4),2,1),"Mar",DATE(RIGHT(DimTable[Month Period],4),3,1),"Apr",DATE(RIGHT(DimTable[Month Period],4),4,1),"May",DATE(RIGHT(DimTable[Month Period],4),5,1),"Jun",DATE(RIGHT(DimTable[Month Period],4),6,1),"Jul",DATE(RIGHT(DimTable[Month Period],4),7,1),"Aug",DATE( RIGHT(DimTable[Month Period],4),8,1),"Sep",DATE(RIGHT(DimTable[Month Period],4),9,1),"Oct",DATE(RIGHT(DimTable[Month Period],4),10,1),"Nov",DATE( RIGHT(DimTable[Month Period],4),11,1),"Dec",DATE(RIGHT(DimTable[Month Period],4),12,1))

Then replace Month Period as this new column DateNew in matrix Columns section, enable Date hierarchy and only keep Year and Month. 

Update: Please use dummy data in PBIX file to protect your sensitive infomration. 

Best Regards,
Qiuyun Yu

[mdaamirkhan · Accepted solution]

Thanks a lot for helping me but For Dynamic change Axis Month to QTR / QTR to Month switch with sorting by Month name I have wrote below dax query:

DimTable =
ADDCOLUMNS(
UNION ( CROSSJOIN(DISTINCT('Month'[Month Period]), DATATABLE("AxisDimension", STRING, { {"Month"}}), DISTINCT(Quarter[Quarter Period])),
CROSSJOIN(DISTINCT('Month'[Month Period]),DATATABLE("AxisDimension", STRING,{ {"QTR"}}), DISTINCT('Quarter'[Quarter Period])))
,"AxisValue", SWITCH([AxisDimension], "QTR", FORMAT(Quarter[Quarter Period], "0000"),"Month", 'Month'[Month Period])
,"AxisSort", SWITCH([AxisDimension], "QTR", CONCATENATE( RIGHT(Quarter[Quarter Period],4), CONCATENATE("0", RIGHT(LEFT(Quarter[Quarter Period],2),1))) ,"Month", CONCATENATE( RIGHT('Month'[Month Period],4), SWITCH(LEFT('Month'[Month Period],3), "Jan", "01", "Feb", "02", "Mar", "03", "Apr", "04", "May", "05", "Jun", "06", "Jul", "07", "Aug", "08", "Sep", "09", "Oct", "10", "Nov", "11", "Dec", "12")))
)

[jthomson]

That's the same question you asked in the initial post and the answer is the same as in the initial reply.

[mdaamirkhan]

I did that not working if you can work on my pbix file it wil helpful so I can understand where I am doing wrong

[mdaamirkhan]

Help me 

[Anonymous]

Hi @mdaamirkhan,

In this article it is clearly explained, they also attached a PBIX file.

https://blogs.msdn.microsoft.com/samlester/2017/04/02/sorting-by-month-names-in-power-bi/

Regards,

L.Meijdam

[mdaamirkhan]

Hi,

I have messed up if you kindly create on my pbix file which I have shared it will be helpful

[Anonymous]

Hi @mdaamirkhan,

I typed this message on my mobile phone so I am affraid I can't do that, my advice is to create a new PBIX and try to get it to work there following the steps from the article. If you can make it work with some sample data in a new PBIX you can do it to your own more advanced PBIX too with that knowledge.

Plus the added value is that after this you yourself knows how to do it, you won't achieve that if you let someone else do it for you.

Regards,

L.Meijdam

[mdaamirkhan]

How Sort number created if it has 3 years data

[Anonymous]

Hi @mdaamirkhan,

I just looked at your PBIX file

 

I think you can just make a column next to it that goes from 1-36 after that you can sort on that column that should probably give what you want.

Regards,

L.Meijdam

[mdaamirkhan]

Can you please please help me on this on my pbix file it will be great 

[Anonymous]

Hi @mdaamirkhan,

If you add a column like this:

You can configure your visual like this:

If  you follow these 2 steps it should work fine in your situation, I believe you can do that yourself.

Regards,

L.Meijdam

[mdaamirkhan]

can you please share the file with this please and how you create a ID

[Anonymous]

Hi @mdaamirkhan,

I made the ID by hand in excel and used the option "Enter data" where I just pasted the below data. If you paste it the "Month" and "ID" will automatically be promoted to column headers.

Month	ID
Jan-15	2015-01
Feb-15	2015-02
Mar-15	2015-03
Apr-15	2015-04
May-15	2015-05
Jun-15	2015-06
Jul-15	2015-07
Aug-15	2015-08
Sep-15	2015-09
Oct-15	2015-10
Nov-15	2015-11
Dec-15	2015-12
Jan-16	2016-01
Feb-16	2016-02
Mar-16	2016-03
Apr-16	2016-04
May-16	2016-05
Jun-16	2016-06
Jul-16	2016-07
Aug-16	2016-08
Sep-16	2016-09
Oct-16	2016-10
Nov-16	2016-11
Dec-16	2016-12
Jan-17	2017-01
Feb-17	2017-02
Mar-17	2017-03
Apr-17	2017-04
May-17	2017-05
Jun-17	2017-06
Jul-17	2017-07
Aug-17	2017-08
Sep-17	2017-09
Oct-17	2017-10
Nov-17	2017-11
Dec-17	2017-12

(it is important that the column "ID" is in Text format not in Date format !) 

Regards,

L.Meijdam

[mdaamirkhan]

can you share the pbix 

[Anonymous]

Hi @mdaamirkhan,

I am afraid I cannot but what do you need it for, you have all the information you need ? Is there something you don't understand ?

Regards,

L.Meijdam

[mdaamirkhan]

Its not working that why I need help if you do on my pbix file and share with me then it will be easy to understand. Kindly please me.