I have a Line Graph in PowerBI where I display the Revenue by Month for the past 5 years (so there are 5 lines) and MonthName is on the x-axis.
Right now I am using the following measure for each year:
Revenue (2016) := CALCULATE( SUM( Register[Total] ), 'Date'[CalendarYear]=2016)
So what I want to do is use dynamic measures where instead of having a measure for 2016, 2015, 2014...etc. I have a measure for the current year, the previous year, the year before that, etc...
Any advice?
You're very close, as far as I can tell.
In your Date table, if you don't already have one, you'll need a CalendarMonthName column to represent month without the year (i.e. Jan, Feb, Mar). You'll also want a second column with numbered months, so you can sort the month names correctly - otherwise, the months appear alphabetically.
Then, you'd have a measure for Revenue (which would be the SUM(Register[Total]) as you have above, but without the CalendarYear filter.
Your line chart would then have Revenue as the "Values", "CalendarMonthName" as the axis, and "CalendarYear" as the Legend. This will create a different colored line for each year, plotted by month (Jan-Dec).
If you want to filter to just 5 years worth of data (e.g. you have 10 years of data and you only want the last 5), you could have an additional true/false column or calculated column in your Date table for the years you want to include, then filter on that column in Power BI.
