Forum Discussion

FrugalEconomist's avatar
FrugalEconomist
Helper III
10 years ago
Solved

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 for...
  • FrugalEconomist's avatar
    FrugalEconomist
    10 years ago

    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's avatar
    FrugalEconomist
    10 years ago

    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

Rose_T's avatar
Rose_T
Frequent Visitor
3 years ago

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