Forum Discussion

joshuajayyyyy's avatar
joshuajayyyyy
New Member
2 years ago
Solved

Formatting a table in a report

Hi I am new to powerbi and was hoping to get some help with a table visualisation.

 

I am using months for this table and it reads off the calendar year instead of my intended fiscal year starting from Oct. I am hoping to change this so it shows October at the top instead of January.

 

Am currently using editing this using the browser function as it is a published report

Need Help
Show and Tell
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    A problem with a fairly straight forward solution fortunately.  Each column has a sort by order.  By default a text column will sort alphabetically but you can click on a column in your model and tell it to use a different "Sort By Column".  Since your Month column is already sorting Jan to Dec, which is not alphabetical, then its likely its been set to sort by Month Number.

     

    So what you need to do is create a new column in that table, which i'm assuming is a calendar table, and have that column be your fiscal month number, where October = 1 and September = 12.  Then you just need to update your month names to sort by this new column.

     

    Consideration:  Sometimes you want your month labels to sort by Calendar month still.  Maybe its worth having a brand new Month called 'Fiscal Month' which has the same text as your month name but has the Fiscal Month sorting instead.

  • suparnababu8's avatar
    suparnababu8
    Icon for Super User rankSuper User
    2 years ago

    Hi joshuajayyyyy 

    To adjust your table to reflect a fiscal year starting in October, you can create a custom column in your date table to represent the fiscal month. Here’s a DAX formula to help you achieve this:

    FiscalMonth =
    IF (
    MONTH([Date]) >= 10,
    MONTH([Date]) - 9,
    MONTH([Date]) + 3
    )

    -->Sort Your Month Column by Fiscal Month

     

    • After creating the FiscalMonth column, go to the “TableView” tab.
    • Select your original month column ( MonthName).
    • Click on “Sort by Column” and choose FiscalMonth.

    Now you'll get your expected outptut as mentioned below.

    If you need more information pls go through this https://community.fabric.microsoft.com/t5/Desktop/DAX-Formulas-for-Fiscal-Year-Fiscal-Quarter-and-Month-Short-Name/m-p/18268

     

    Thanks!