Month sorting for a dynamic axis chart

[Winniethewinner]

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. 

[Anonymous]

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]

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: