Forum Discussion
Sorting by Fiscal Month
Dear Community,
I'm trying to report using a fiscal calendar (7/1 - 6/30) and sort it by month. However month is being sorted alphabetically by default. My month field is calculated using the formula DateTime.ToText([Date],"MMM"))
Is it possible to resort the graph so that Jul is first?
Thank you
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
10 Replies
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 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?
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!
This is how i solved this issue.
My Fiscal Year is Apr to March.
1. Click on Transform Data
2. Choose your Date Table
3. Next i created a new Column
a. In your Table, choose the column that has the Date
b. Click on Add Column on the top of the ribbon
c. Extract Month Name from Date Column - See BelowExtract Month Name from Date
Note: Power Bi adds a new Month Name column
4. Duplicate the Month name column,
5. Rename the duplicated fiscal Month No.
6. Next i replaced this month with the numbers
a. since the start of my fiscal month is april
april = 1
may = 2
June = 3 etc
7. Convert the Data type of the fiscal month number to whole Number - VERY IMPORTANT !!!!
8. Close and apply the changes
9. In the Data view, choose the Month Column and Sort that month column by the Fiscal Month No. Column you just create.
Good Luck Data Nerds
