Sorting by Fiscal Month

[FrugalEconomist]

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

[FrugalEconomist · Accepted solution]

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

[memo_olarte]

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!

[Rose_T]

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

[FrugalEconomist · Accepted solution]

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

[FrugalEconomist · Accepted solution]

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

[Anonymous]

---

@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 0

---

I 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.

[id1]

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?

[prasannavp]

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.

[CMcAlpine]

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?

[ccsm_powerbi]

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.

[Anonymous]

do you have a Month Number ( Integer ) field 

if so , you can sort the month using Month Number coumn