Forum Discussion
Solved
Why won't my Sort By column work?!
Trying to sort my Fiscal Month & Year by my Fiscal Month and Year KEY, which are sequential numbers formatted as text? Keep getting the error, "Can not sort by column that is either directly or i...
Hi CoreyP
From your pbix, I found that the column “FiscMon&YrKEY” is created by a IF condition sentence using column “FiscMon&YrKEY”, so when you sort column “Fiscal Month & Year” by column “FiscMon&YrKEY”, the error happens.
I use columns 'Custom Date'[Fiscal Month] and 'Custom Date'[Fiscal Year] to create “FiscMon&YrKEY” instead, then I can get the same column as before and are able to sort column “Fiscal Month & Year” by column “FiscMon&YrKEY”.
FiscMon&YrKEY = IF(AND('Custom Date'[Fiscal Month] ="January",'Custom Date'[Fiscal Year]="2017"),"1", IF(AND('Custom Date'[Fiscal Month] ="February",'Custom Date'[Fiscal Year]="2017"),"2", IF(AND('Custom Date'[Fiscal Month] ="March",'Custom Date'[Fiscal Year]="2017"),"3", IF(AND('Custom Date'[Fiscal Month] ="April",'Custom Date'[Fiscal Year]="2017"),"4", IF(AND('Custom Date'[Fiscal Month] ="May",'Custom Date'[Fiscal Year]="2017"),"5", IF(AND('Custom Date'[Fiscal Month] ="June",'Custom Date'[Fiscal Year]="2017"),"6", IF(AND('Custom Date'[Fiscal Month] ="July",'Custom Date'[Fiscal Year]="2017"),"7", IF(AND('Custom Date'[Fiscal Month] ="August",'Custom Date'[Fiscal Year]="2017"),"8", IF(AND('Custom Date'[Fiscal Month] ="September",'Custom Date'[Fiscal Year]="2017"),"9", IF(AND('Custom Date'[Fiscal Month] ="October",'Custom Date'[Fiscal Year]="2017"),"10", IF(AND('Custom Date'[Fiscal Month] ="November",'Custom Date'[Fiscal Year]="2017"),"11", IF(AND('Custom Date'[Fiscal Month] ="December",'Custom Date'[Fiscal Year]="2017"),"12", IF(AND('Custom Date'[Fiscal Month] ="January",'Custom Date'[Fiscal Year]="2018"),"13", IF(AND('Custom Date'[Fiscal Month] ="February",'Custom Date'[Fiscal Year]="2018"),"14", IF(AND('Custom Date'[Fiscal Month] ="March",'Custom Date'[Fiscal Year]="2018"),"15", IF(AND('Custom Date'[Fiscal Month] ="April",'Custom Date'[Fiscal Year]="2018"),"16", IF(AND('Custom Date'[Fiscal Month] ="May",'Custom Date'[Fiscal Year]="2018"),"17", IF(AND('Custom Date'[Fiscal Month] ="June",'Custom Date'[Fiscal Year]="2018"),"18", IF(AND('Custom Date'[Fiscal Month] ="July",'Custom Date'[Fiscal Year]="2018"),"19", IF(AND('Custom Date'[Fiscal Month] ="August",'Custom Date'[Fiscal Year]="2018"),"20", IF(AND('Custom Date'[Fiscal Month] ="September",'Custom Date'[Fiscal Year]="2018"),"21", IF(AND('Custom Date'[Fiscal Month] ="October",'Custom Date'[Fiscal Year]="2018"),"22", IF(AND('Custom Date'[Fiscal Month] ="November",'Custom Date'[Fiscal Year]="2018"),"23", IF(AND('Custom Date'[Fiscal Month] ="December",'Custom Date'[Fiscal Year]="2018"),"24","error" ))))))))))))))))))))))))Best Regards
Maggie
I suspect the index column you are trying to sort this column by does not have a 1-1 unique mapping
You might have a January 2017 or a January 2019 that maps to that same numbers
usually when you want to the months in chronological order, you should create an index column in the following format, 012019 022019 .... 122019
Thanks for the reply's guys! Let me show you some of my code:
Custom Date = CALENDAR(DATE( 2017, 12, 26 ), DATE(2018, 12, 25))
Day of Month = DAY('Custom Date'[Date])Day of Week = FORMAT('Custom Date'[Date], "DDDD")Fiscal Month = IF(AND('Custom Date'[Date] >= DATE(2016, 12, 26), 'Custom Date'[Date] <= DATE(2017, 1, 25)), "January", IF(AND('Custom Date'[Date] >= DATE(2017, 1, 26), 'Custom Date'[Date] <= DATE(2017, 2, 25)), "February", IF(AND('Custom Date'[Date] >= DATE(2017, 2, 26), 'Custom Date'[Date] <= DATE(2017, 3, 25)), "March", IF(AND('Custom Date'[Date] >= DATE(2017, 3, 26), 'Custom Date'[Date] <= DATE(2017, 4, 25)), "April", IF(AND('Custom Date'[Date] >= DATE(2017, 4, 26), 'Custom Date'[Date] <= DATE(2017, 5, 25)), "May", IF(AND('Custom Date'[Date] >= DATE(2017, 5, 26), 'Custom Date'[Date] <= DATE(2017, 6, 25)), "June", IF(AND('Custom Date'[Date] >= DATE(2017, 6, 26), 'Custom Date'[Date] <= DATE(2017, 7, 25)), "July", IF(AND('Custom Date'[Date] >= DATE(2017, 7, 26), 'Custom Date'[Date] <= DATE(2017, 8, 25)), "August", IF(AND('Custom Date'[Date] >= DATE(2017, 8, 26), 'Custom Date'[Date] <= DATE(2017, 9, 25)), "September", IF(AND('Custom Date'[Date] >= DATE(2017, 9, 26), 'Custom Date'[Date] <= DATE(2017, 10, 25)), "October", IF(AND('Custom Date'[Date] >= DATE(2017, 10, 26), 'Custom Date'[Date] <= DATE(2017, 11, 25)), "November", IF(AND('Custom Date'[Date] >= DATE(2017, 11, 26), 'Custom Date'[Date] <= DATE(2017, 12, 25)), "December", IF(AND('Custom Date'[Date] >= DATE(2017, 12, 26), 'Custom Date'[Date] <= DATE(2018, 1, 25)), "January", IF(AND('Custom Date'[Date] >= DATE(2018, 1, 26), 'Custom Date'[Date] <= DATE(2018, 2, 25)), "February", IF(AND('Custom Date'[Date] >= DATE(2018, 2, 26), 'Custom Date'[Date] <= DATE(2018, 3, 25)), "March", IF(AND('Custom Date'[Date] >= DATE(2018, 3, 26), 'Custom Date'[Date] <= DATE(2018, 4, 25)), "April", IF(AND('Custom Date'[Date] >= DATE(2018, 4, 26), 'Custom Date'[Date] <= DATE(2018, 5, 25)), "May", IF(AND('Custom Date'[Date] >= DATE(2018, 5, 26), 'Custom Date'[Date] <= DATE(2018, 6, 25)), "June", IF(AND('Custom Date'[Date] >= DATE(2018, 6, 26), 'Custom Date'[Date] <= DATE(2018, 7, 25)), "July", IF(AND('Custom Date'[Date] >= DATE(2018, 7, 26), 'Custom Date'[Date] <= DATE(2018, 8, 25)), "August", IF(AND('Custom Date'[Date] >= DATE(2018, 8, 26), 'Custom Date'[Date] <= DATE(2018, 9, 25)), "September", IF(AND('Custom Date'[Date] >= DATE(2018, 9, 26), 'Custom Date'[Date] <= DATE(2018, 10, 25)), "October", IF(AND('Custom Date'[Date] >= DATE(2018, 10, 26), 'Custom Date'[Date] <= DATE(2018, 11, 25)), "November", IF(AND('Custom Date'[Date] >= DATE(2018, 11, 26), 'Custom Date'[Date] <= DATE(2018, 12, 25)), "December", "Errr"))))))))))))))))))))))))Fiscal Quarter = IF('Custom Date'[Fiscal Month] = "January", "Q1", IF('Custom Date'[Fiscal Month] = "February", "Q1", IF('Custom Date'[Fiscal Month] = "March", "Q1", IF('Custom Date'[Fiscal Month] = "April", "Q2", IF('Custom Date'[Fiscal Month] = "May", "Q2", IF('Custom Date'[Fiscal Month] = "June", "Q2", IF('Custom Date'[Fiscal Month] = "July", "Q3", IF('Custom Date'[Fiscal Month] = "August", "Q3", IF('Custom Date'[Fiscal Month] = "September", "Q3", IF('Custom Date'[Fiscal Month] = "October", "Q4", IF('Custom Date'[Fiscal Month] = "November", "Q4", IF('Custom Date'[Fiscal Month] = "December", "Q4", "Errr") )))))))))))Fiscal Year = IF(AND('Custom Date'[Date] >= DATE(2016, 12, 26), 'Custom Date'[Date] <= DATE(2017, 12, 25)), "2017", "2018")Fiscal Month & Year = 'Custom Date'[Fiscal Month]&" "&'Custom Date'[Fiscal Year]
Fiscal QuarterYear = 'Custom Date'[Fiscal Year]&'Custom Date'[Fiscal Quarter]
FiscMon&YrKEY = IF('Custom Date'[Fiscal Month & Year] = "January 2017", "1", IF('Custom Date'[Fiscal Month & Year] = "February 2017", "2", IF('Custom Date'[Fiscal Month & Year] = "March 2017", "3", IF('Custom Date'[Fiscal Month & Year] = "April 2017", "4", IF('Custom Date'[Fiscal Month & Year] = "May 2017", "5", IF('Custom Date'[Fiscal Month & Year] = "June 2017", "6", IF('Custom Date'[Fiscal Month & Year] = "July 2017", "7", IF('Custom Date'[Fiscal Month & Year] = "August 2017", "8", IF('Custom Date'[Fiscal Month & Year] = "September 2017", "9", IF('Custom Date'[Fiscal Month & Year] = "October 2017", "10", IF('Custom Date'[Fiscal Month & Year] = "November 2017", "11", IF('Custom Date'[Fiscal Month & Year] = "December 2017", "12", IF('Custom Date'[Fiscal Month & Year] = "January 2018", "13", IF('Custom Date'[Fiscal Month & Year] = "February 2018", "14", IF('Custom Date'[Fiscal Month & Year] = "March 2018", "15", IF('Custom Date'[Fiscal Month & Year] = "April 2018", "16", IF('Custom Date'[Fiscal Month & Year] = "May 2018", "17", IF('Custom Date'[Fiscal Month & Year] = "June 2018", "18", IF('Custom Date'[Fiscal Month & Year] = "July 2018", "19", IF('Custom Date'[Fiscal Month & Year] = "August 2018", "20", IF('Custom Date'[Fiscal Month & Year] = "September 2018", "21", IF('Custom Date'[Fiscal Month & Year] = "October 2018", "22", IF('Custom Date'[Fiscal Month & Year] = "November 2018", "23", IF('Custom Date'[Fiscal Month & Year] = "December 2018", "24", "Errr"))))))))))))))))))))))))Month & Year = FORMAT('Custom Date'[Date], "MMM")&" "&YEAR('Custom Date'[Date])Month Name = FORMAT('Custom Date'[Date], "MMMM")Month Number = MONTH('Custom Date'[Date])Weekday Number = WEEKDAY('Custom Date'[Date],2)Week Number = WEEKNUM('Custom Date'[Date],2)Week Ending = 'Custom Date'[Date] + (7 - 'Custom Date'[Weekday Number])
Also tried making a new KEY column just to see if that would do it, but nope.. same error.
FiscalKEY = IF( 'Custom Date'[Fiscal Month & Year] = "January 2018", "01" , IF( 'Custom Date'[Fiscal Month & Year] = "February 2018", "02" , IF( 'Custom Date'[Fiscal Month & Year] = "March 2018", "03" , IF( 'Custom Date'[Fiscal Month & Year] = "April 2018", "04" , IF( 'Custom Date'[Fiscal Month & Year] = "May 2018", "05" , IF( 'Custom Date'[Fiscal Month & Year] = "June 2018", "06" , IF( 'Custom Date'[Fiscal Month & Year] = "July 2018", "07" , IF( 'Custom Date'[Fiscal Month & Year] = "August 2018", "08" , IF( 'Custom Date'[Fiscal Month & Year] = "September 2018", "09" , IF( 'Custom Date'[Fiscal Month & Year] = "October 2018", "10" , IF( 'Custom Date'[Fiscal Month & Year] = "November 2018", "11" , IF( 'Custom Date'[Fiscal Month & Year] = "December 2018", "12" , "00" ))))))))))))
