Show "base" year in multi-year line chart

[apohl1]

Hi all,

I would like to create a line chart as first chart below, one line showing revenue by month over two or more years, but I would like a second line in the chart that shows sales by month for a "base" year. "Base" year would be defined as 2017 sales. I am able to set this up for one year view (2nd chart) or alternaively add more lines to the 2nd chart to show additional years but that's not what I want. How do I create a measure that shows sales by month in a previous year (2017) that can be shown in the line chart below covering sales by month for years 2020-2021?

Thanks in advance!

[Whitewater100]

Hi:

If I follow you, than your X axis should just be Month Name. Then each year (2020 & 2021) is a separate measure put on the chart.

To obtain the mixed line you can do something like:

Reference Line(Bal of 2017) = 
VAR = LastDatewithSales = Calculate(MAX(Sales[Order Date]), REMOVEFILTERS())
VAR = LineBalance = CALCULATE[2017 Sales], KEEPFILTERS(Dates[Date]) >= LastDatewithSales
return
LineBalance

This line would start where the grey line ends,assuming the grey line is 2021 and the year was not complete.

I hope this helps you.

[amitchandak]

@apohl1 , One is you select two years, Use month on axis and year on the legend 

second

you can use trailing measure use date table and time intelligence

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

[apohl1]

Thank you for the suggestion but I want x-axis to show Year-month (from 2020 and onwards) and base year sales should show 2017 sales by month and not previous year's sales (base year sales should be the same for April 2020 and April 2021). I hope this clarifies!

[amitchandak]

@apohl1 , Not very clear. 3 year behind

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-3,Year))

[apohl1]

What I want to create is the red line in the chart below. The red line is the grey line continued as a reference point for all future years.  I hope this clarifies.