Year over Year rolling 12 months

[Anonymous]

Hi,

I want to create a visual where I compare months this year to the same months last year. This part causes no problems. However, I want to make the visual dynamic such that it shows the current month at the end of the x-axis and rolling -12 months back in time. So far I have tried two different approaches: 

1) 

This one somewhat gives me the desired outcome, but how do I get rid of the tails? I want the visual to show one bar with values representing December 2019 to November 2020 (today) and one bar showing values from December 2018 to November 2019 for monthly comparison. Additionally it has to be dynamic.

2)

In this visual I only use the relevant fields with filters and no measure. This one gives me the desired outcome, but how do I sort it chronologically to give med the current month (November) at the end of the x-axis? I have tried to sort the field MonthName (x-axis) with MonthNumber in my date dimension, only to make the visual sort wither from month 12 to 1 or from 1 to 12. 

Any advise? Much appreciated!

BR

[Anonymous]

Anyone else having a suggestion?

Thanks 🙂

[Anonymous · Accepted solution]

@Anonymous 
You might create 2 measures, measure 1 between today and - 365 days , and measure 2 is between - 365 days and -730 days. Something like:

measure 1 = Calculate(sum([value]), filter(table, [date]<=today() &&[date]>=today()-365))

measure 2 = Calculate(sum([value]), filter(table, [date]<=today()-365 &&[date]>=today()-365*2))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Not very clear , if want sorting to desc create column like these and mark them sort colum

Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")
Month Year Rank = RANKX(ALL('Date'),'Date'[Month Year Sort],,DESC,Dense)

Sort month Year on Month Year Rank  in place of Month Year sort

Month = FORMAT([Date],"mmmm")
Month sort = month([Date])
Month Rank = RANKX(ALL('Date'),'Date'[Month Year Sort],,DESC,Dense)

Sort month on Month Rank  in place of Month sort

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

expected output is not very clear to me

[Anonymous]

Thanks for your reply, amitchandak!

Your suggested method provides the same output as my second approach described above i.e. it sorts from December to January or January to December. How can I make it sort from November (current month) and 12 months back (November 2019)?

Thanks!