Forum Discussion

DavidWaters100's avatar
DavidWaters100
Icon for Post Patron rankPost Patron
6 years ago
Solved

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

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

 

 

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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.

4 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
    DavidWaters100
    Icon for Post Patron rankPost Patron
    6 years ago

    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's avatar
      DavidWaters100
      Icon for Post Patron rankPost Patron
      6 years ago

      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...