Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Need help updating graph sort order

Hi, I want my graph to be the UK financial year April24 - March25.

 

I've made a column called Month_Number that says April = 1, May = 2, etc.

I then changed the sort order of the Month_Name to Month_Number and used Month_Name in my X-Axis.

But it's still giving me this order, I need January to be placed after December please:

 


Thanks for your help

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi RichOB 

     

    Please try this:

    Here I create a set of sample:

    Table with data from 2024,1,1 to 2025,12,1:

    Then add a calculated table:

    Table 2 = 
SUMMARIZE (
    SELECTCOLUMNS (
        'Table',
        "Month_name", FORMAT ( 'Table'[Date], "MMM" ),
        "Month_number",
            VAR _month =
                MONTH ( 'Table'[Date] ) - 3
            RETURN
                IF ( _month <= 0, _month + 12, _month )
    ),
    [Month_name],
    [Month_number]
)

    Select the [Month_name] and click sort by [Month_number]:

     

    Connect the Table and Table 2 with the field Month_name:

    The result is as follow:

     

    .PBIX file has attached.

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    RichOB , Try using

    Ensure that your Month_Number column correctly reflects the custom order where April is 1 and March is 12. You can create this column in Power BI using DAX.

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

     

    In Power BI, you need to sort the Month_Name column by the Month_Number column. 

    Go to the Data view.
    Select the Month_Name column.
    In the ribbon, click on the "Sort by Column" button.
    Choose Month_Number from the dropdown list.

     

    Now, when you use Month_Name in your X-Axis, it should follow the custom order defined by Month_Number.

     

  • RichOB's avatar
    RichOB
    Icon for Post Partisan rankPost Partisan
    1 year ago

    HI bhanu_gautam thanks for your reply. 

     

    Your solution makes sense and I followed the steps, but when I'm sorting the Month_Name column by Month Number, I'm getting an Error message saying A Circular Dependency Was Detected and it's not sorting the column. Any ideas how to fix this please?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi RichOB 

     

    Please try this:

    Here I create a set of sample:

    Table with data from 2024,1,1 to 2025,12,1:

    Then add a calculated table:

    Table 2 = 
SUMMARIZE (
    SELECTCOLUMNS (
        'Table',
        "Month_name", FORMAT ( 'Table'[Date], "MMM" ),
        "Month_number",
            VAR _month =
                MONTH ( 'Table'[Date] ) - 3
            RETURN
                IF ( _month <= 0, _month + 12, _month )
    ),
    [Month_name],
    [Month_number]
)

    Select the [Month_name] and click sort by [Month_number]:

     

    Connect the Table and Table 2 with the field Month_name:

    The result is as follow:

     

    .PBIX file has attached.

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.