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
Here is a much simpler solution. Learned this from my C++ days.
I also added an additional column:
The following is the equation:
The basic equation for any month start is:
FY Month Order = MOD(MONTH([Date]) + a , 12 ) + 1
%% Where "a" is how much you need to add to your month start to make it equal 12 ( ie. For November, a = 1 )
4 Steps to this function:
1. Month(), Capture month numerical value
2. add "a" to month numerical value
3. Find the "month + a" modulus of 12
4. Add 1 to slide numbers in the right order.
Below is how each step occurs if you're curious:
Cheers!