Forum Discussion

James_White_199's avatar
James_White_199
Frequent Visitor
1 year ago
Solved

Help with sorting PowerBI Visuals not by calendar date

Hi All    I need some help trying to arrange a powerBI Visual Stacked Column Chart in accordance with my financial year as opposed to calendar year.  All my charts currently run from January to De...
  • bhanu_gautam's avatar
    1 year ago

    James_White_199 Add a new column in your data model to represent the fiscal year and fiscal month. You can do this using DAX 

    DAX
    FiscalYear = IF(MONTH([Date]) >= 10, YEAR([Date]), YEAR([Date]) - 1)
    FiscalMonth = MONTH([Date]) - 9
    FiscalMonth = IF(FiscalMonth <= 0, FiscalMonth + 12, FiscalMonth)

     

    Ensure that the fiscal months are sorted correctly. You can create a custom sort order for the fiscal months.

    DAX
    FiscalMonthName = SWITCH([FiscalMonth],
    1, "October",
    2, "November",
    3, "December",
    4, "January",
    5, "February",
    6, "March",
    7, "April",
    8, "May",
    9, "June",
    10, "July",
    11, "August",
    12, "September"
    )

     

    Then, sort the FiscalMonthName column by the FiscalMonth column.

     

    Replace the calendar year and month in your visual with the new fiscal year and fiscal month columns.

     

    Ensure that the axis of your Stacked Column Chart uses the FiscalYear and FiscalMonthName columns to reflect the correct order.