Forum Discussion
Sorting by Fiscal Month
I figured it out. Maybe it's clunky, but it solved what I was looking for.
I created another column using the formula below. And then I sorted my [Month] variable in the modeling tab by my new column:
if [Month] = "Jan" then 7
else if [Month] = "Feb" then 8
else if [Month] = "Mar" then 9
else if [Month] = "Apr" then 10
else if [Month] = "May" then 11
else if [Month] = "Jun" then 12
else if [Month] = "Jul" then 1
else if [Month] = "Aug" then 2
else if [Month] = "Sep" then 3
else if [Month] = "Oct" then 4
else if [Month] = "Nov" then 5
else if [Month] = "Dec" then 6
else 0Actually the above code doesn't work. Power BI sorts 1, 10, 11, 12, 2, ....
I changed it to alphabets
if [Month] = "Jan" then "G"
else if [Month] = "Feb" then "H"
else if [Month] = "Mar" then "I"
else if [Month] = "Apr" then "J"
else if [Month] = "May" then "K"
else if [Month] = "Jun" then "L"
else if [Month] = "Jul" then "A"
else if [Month] = "Aug" then "B"
else if [Month] = "Sep" then "C"
else if [Month] = "Oct" then "D"
else if [Month] = "Nov" then "E"
else if [Month] = "Dec" then "F"
else 0
Actually the above code doesn't work. Power BI sorts 1, 10, 11, 12, 2, ....
I changed it to alphabets
if [Month] = "Jan" then "G"
else if [Month] = "Feb" then "H"
else if [Month] = "Mar" then "I"
else if [Month] = "Apr" then "J"
else if [Month] = "May" then "K"
else if [Month] = "Jun" then "L"
else if [Month] = "Jul" then "A"
else if [Month] = "Aug" then "B"
else if [Month] = "Sep" then "C"
else if [Month] = "Oct" then "D"
else if [Month] = "Nov" then "E"
else if [Month] = "Dec" then "F"
else 0
Im having the same problem, but im' not using a custom "month" field. I'm using the inbult Power BI date heirachy and it's refusing to sort by my fiscal month column. Basically each row in my dataset has a column for "Fiscal Month" that orders it by 01, 02 etc based upon the fiscal year, but sorting it via the column sort does nothing when my visual is using the month heirarchy attribute.
Any ideas?
I have the same problem here. I use a date table with calendar and fiscal months. When I sort the month name by the calendar month, it's ok but when I sort the month name by the fiscal month, the month appear in a strange order in my visualization.
