Month sort difficulties when excluding year

[shaunwilks]

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

[AlbertoFerrari]

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

[TomMartens]

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 

[shaunwilks]

Thanks for the suggestion but this returns the same error.

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.

[TomMartens · Accepted solution]

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