Today I will show you how to create the chart which allows comparing Sales of the current year vs the previous year, where you can change the current year using the parameter.
1. Create a Year parameter.
2. Create a calculated field [Current period] where DATEPART(‘year’, [Order Date]) = [Year parameter]
3. Create a calculated field [Same period last year], where YEAR([Order Date])= [Year parameter]-1
4. Create a calculated field [Last year sales ], where if [Same period last year] then [Sales] else 0 end
5. Create a calculated field [This year Sales], where if [Current period] then [Sales] else 0 end
6. Create a calculated field [Difference btw years], where (SUM([this year sales])-SUM([Last year Sales]))/SUM([Last year Sales])
Bring [Same period last year], [Last year sales ], [Difference btw years] to columns; bring Segment to rows, show Year parameter
You should end up with something like below chart.
The downfall of this chart is that it’s too wide and maybe won’t look good on your dashboard. Also it’s not very easy to see the difference in numbers between the years. To make it a bit more compact we can use a dual axis for the [last year] pill and change the chart type in the Mark card for the last year sales to Gantt Bar.
Now the chart is easily readable and you have the opportunity to dynamically change the year for comparison.