Forum Discussion

Winniethewinner's avatar
Winniethewinner
Helper IV
4 years ago

Month sorting for a dynamic axis chart

Hi everyone, 

I created a dynamic axis chart by using this approach: View solution in original post

The values in dimension table is Text: 

Is there a way to add a column with Month name of Jan - Dec, and sort it properly in the chart?

Thank you. 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Winniethewinner ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated table named Date.

    Date =
CALENDAR(DATE(2021,1,1),DATE(2021,12,31))

    Create calculated column in Date table.

    month =
FORMAT('Date'[Date],"m" )
    month_flag = FORMAT('Date'[Date],"mmm")

    2. Create calculated table named Sort_table

    Sort_table =
SUMMARIZE('Date','Date'[month],'Date'[month_flag])
    Create calculated column in Sort_table table.
rank = RANKX(ALL('Sort_table'),VALUE('Sort_table'[month]),,ASC)

    Check [month_flag] - Column tools - Sort by column - [rank] in the Sort_table table

    3. Connect Table with Sort_Table.

    4. Result:

    Please click here for the pbix file

     

    Best Regards,

    Liu Yang

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

    • Winniethewinner's avatar
      Winniethewinner
      Helper IV
      4 years ago

      Thanks for your suggestion Anonymous . 

      I'm trying out your solution and have 2 quesitons: 

      1. Your Table has 2x Attributes: Month and DAYOFWEEK. If the DAYOFWEEK has values of 1,2,3,4,5,6,7, how would you make the chart Axis to be switched between month_flag and DAYOFWEEK?   

      2. For some reason, after I entered calculated column Rank = RANKX(ALL('Sort_Table'),VALUE('Sort_Table'[Month]),,ASC) in the Sort_Table, it starts with 2 instead of 1: