Unable to rank months by what is selected on slicer

[DavidWaters100]

Hi,

Got a problem with ranking months on a graph which is absulutely killing my time!

I have two rolling 12 calculations per below which respond to a slicer in the format MMMM YYYY from the calendar table which is linked to the date field in my table.  I have the month on the X axis on the graph without the year so that the two amounts appear side by side.  If I involve the year, the graph spreads out over 24 months but I only want 12 months.

The numbers are working on the 12 months however the major problem for me is that the graph is ranked Jan to December.  I need the month ranking to start (or end) from whichever month the user selected.  So if March 2020 is selected, the graph must start or end with March and the other months in order from this.

I have spend so much time this weekend trying to resolve this!  Any help greatly appreciated.

I tried various ranking measures but realised you can't sort a graph by a measure?

Rolling 12 GWP =

CALCULATE (

[Amount],

DATESINPERIOD( 'Calendar Table'[Date], MAX ( 'Calendar Table'[Date] ),-12, MONTH )

)

Rolling 12 GWP =

CALCULATE (

[Last Year Amount],

DATESINPERIOD( 'Calendar Table'[Date], MAX ( 'Calendar Table'[Date] ),-12, MONTH )

)

[MFelix]

Hi @DavidWaters100,

Look at the post below that explains how to have a rolling n months value, you can adjust the month part to be fixed, and also use it with previous years measures.

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...

[DavidWaters100]

Hi Miguel,

Thanks very much for this but if you try to add two values to the graph (this year and last year), it does not work.  I think this example works only for one value?  You can download the file and try

[MFelix]

Hi @DavidWaters100 ,

You are correct. Looking at the post I present below I was abble to adapt the first solution.

http://danscorner4businessintel.blogspot.com/2018/01/showing-last-12-months-based-on-date.html

Check PBIX file attach.

[DavidWaters100]

thanks - I just downloaded, but file seems to be the same as the blog mentioned?  Can you advise which part you adapted please?

[DavidWaters100]

I don't seem to be able to show both this year and last year in the graph which keeps the order of month - if you can, please can you re-send with that graph showing?  Many thanks

[MFelix · Accepted solution]

Hi @DavidWaters100 ,

You are acorrect I have send out the wrong file see attach correct file.

[DavidWaters100]

Hi - I get the below error message when opening the latest file! 

Can you try to attach again?

[DavidWaters100]

Hi - I have managed to open the file now (problem was caused by a different version).

I can see this working in your file which is great, but I have transferred the logic to my file and recieve the below error!  Any ideas please?

[DavidWaters100]

I have a joined calender table - if I swap the date in my data to the date on the calendar table I only get the one selcted month showing on the graph!

[DavidWaters100]

Yes!  This works, I had some logic incorrect which I have now corrected.

Thank you so much - you have freed up the rest of my day!

[MFelix]

Hi @DavidWaters100 ,

Sorry for not replying to your messages. but glad you were abble to figuere out.

Can you please mark the correct answer to help others.

[DavidWaters100]

Hi, My browser will not let me accept solution as answer - it puts up error messages about java not being enabled.  I am using internet explorer but cannot switch browser currently because I need to reset my password and do not have admin rights! 

I will accept your solution as soon as I am able to!

[DavidWaters100]

when you add this year and last year together, because axis has MMMM YYYY, they don't show side by side as the two years are different.  I have to change to MMMM only to allow this to happen, but then the ranking from what the user selected is lost!