Cannot sort by user selected month when axis is only month number

[DavidWaters100]

Hi,

I have two rolling 12 measures for this year and one for last year and it is working per below but the major problem is that because the axis is just the month number without reference to the year, I cannot sort it by the month the user has selected.  In the case below the user has select April 2020.  So orange is April 2020 and dark blue is April 2019.  For May the values are dark blue is May 2019 and light blue May 2018.

So I need April 2020 to be first (or last) depending on the sort order.  I can only use the month number because adding the year to the axis means the columns seperate out chonologically.

I have run out of ideas!  Anyone got a magic workaround?

many thanks!

David

 

[v-zhenbw-msft · Accepted solution]

Hi @DavidWaters100 ,

1. We need to create a new parameter to be a slicer.

2. Then we can create a measure to sort the Month.

Sort = 
RANKX (
    GENERATESERIES ( 1, 12 ),
    IF ( [Value] <= [SelectMonth Value], [Value] + 12, [Value] ),
    IF (
        MIN ( 'Table'[Month] ) <= [SelectMonth Value],
        MIN ( 'Table'[Month] ) + 12,
        MIN ( 'Table'[Month] )
    ),
    DESC,
    DENSE
)

3. At last we can create a clustered column chart, and put the [sort] to Tooltips. And change the X axis type to Categorical. We can get the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[DavidWaters100]

Thank you, great post!

[DavidWaters100]

Sorry I have solved this now.

I added a column in my calendar table which returned the month number chosen by the user, then assigned an order decimal value on my calender table to the 12 month number of all years so that the chosen month had highest value, then the second highest was the previous month number etc. 

I then used LOOKUPVALUES to look up the order value and return it to my table with the graph.  I could then sort by the order value looked up.

[DavidWaters100]

Oh dear, I have not solved this!  My calendar table was bringing in today's month, not the one selected by a user on another table!

I still haven't resolved this...