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
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 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 0Im 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.
I created a column like advised from A to L and sort the table by this column, this helps to display sales from A to L, however when I put onthe x-axis the fiscal month, it goes back to the orignal alphabetic month sort. Please advise.
FrugalEconomist wrote: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 0I have the same problem, with the month, a letter didn't see good. The solution is make the numbers in text.
For example 1 to 01, in the dashboard look better.
Regards.
PST: My skill language is Spanish ;-), I am learning English.
Hi, I am having a similar issue but I do not want 01, 02 etc. displayed on the graph, my client wants the full name of each month. Do you know how I could do this?
