cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shaunwilks Resolver I
Resolver I

Month sort difficulties when excluding year

Best I illustrate with data.

 

 

I have 3 years of Financial Data. The Fiscal year is 01 July - 30 June.

Values for each year are posted with a period number from that year 1-12.

ie July = Period 1, August = Period 2 etc etc etc etc

 

My requirements

1) A chart to have the Month names from July to June along the X axis and compare the values for the 3 financial years against each other

 

2) Another chart that would have the years across the bottom showing the progression of the value across the years.

*This one I am not having any problems with after the suggestions in https://community.powerbi.com/t5/Desktop/Sort-months-in-chronological-order/td-p/65995 - This chart works as I am using the Month-Year field TESTMonthName in the chart.

 

The first chart is working well also - however the months along the x-axis are in alphanumeric order.

I right click and use the "Sort by Column" function on the MonthName column but in every case I get an error suggesting that I cannot have more than one value in the column to sort. Ive tried using the period history sort where the numbers 01-39 exist and are unique. I try sorting the month name using the TESTMonthName column that includes the year and it also fails.

 

How do I get the chart to order the MonthName fields along the bottom in order to compare the three years ?

I dont want to have to use the TestMonthName column.

 

Any help appreciated

Example2.JPG

 

 

Example1.JPG

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Month sort difficulties when excluding year

Hey,

 

no you don't need to create an extra table.

 

Somehow there is a little glitch in your PeriodNbr column, the number 12 is assigned to May in the year 2016 and June in the year 2017. Have a closer look at the datetime may 2016, it's duplicate.

 

If you fix this the error will disappear.

 

Regards

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User III
Super User III

Re: Month sort difficulties when excluding year

Hey,

 

just sort the column MonthName by the column PeriodNbr.

 

You can't use the column PeriodHistorySort because one MonthName eg July gets different sort values 01 and 14.

 

Regards 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Re: Month sort difficulties when excluding year

If you want July to be first and June to be the last, then you need to build a column in your date table that stores 1 for July, 2, for August and so on until you assign 12 to June, the code is pretty simple.

For each month name there need to be only one value in that column. If you add the year, then January will have different values for different years.

I wrote extensively about this in my last modeling book and you can find more information (although I don't remember if we treat sorting of fiscal months) in the Time patterns at DAX Patterns.


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI
shaunwilks Resolver I
Resolver I

Re: Month sort difficulties when excluding year

Thanks for the suggestion but this returns the same error.

 

Example3.JPG

I have done this in another project where only 1 year of financial data was in the table and it works fine - so clearly the issue is because there are three of each month in the MonthName column and there are three of each PeriodNbr in that column.

 

I think the only solution will be to create another table that only stores the month name once and join the table to it using the PeriodNbr.  Ultimately I dont think you should have to do this as the sort on the MonthName column in my example should be allowed by the PeriodNbr as you assumed - it just doesnt allow it.

 

Super User III
Super User III

Re: Month sort difficulties when excluding year

Hey,

 

no you don't need to create an extra table.

 

Somehow there is a little glitch in your PeriodNbr column, the number 12 is assigned to May in the year 2016 and June in the year 2017. Have a closer look at the datetime may 2016, it's duplicate.

 

If you fix this the error will disappear.

 

Regards

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors