Calculate variance for last 5 years depending on the Year selected from the slicer

[Sammy_01]

Hi All,

I have two measures Sales and Performance. I also have a Date(YYYY) column in the same table which is dated until x years i.e 2020, 2019, 2018, 2017, 2016, etc

If I select 2018 from the Date slicer, I need to show last 5 years of Sales in each row of the table. Another row holding the variance between the Sales years.

Example:

	2018	2017	2016	2015	2014
Sales	350	300	275	260	255
var		50	25	15	5

Challenge, I am unable to get the last 5 years data as a column in a table and also find the difference between the sales dynamically. 

Could you please help me as to how I can achieve the solution? thanks

[Icey · Accepted solution]

Hi @Sammy_01 ,

Please check the attached .pbix file.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@Sammy_01 , With a separate year table , you should be able to get diff

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

In matrix use show on row - https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

[Sammy_01]

@amitchandak  thanks for your solution. I am able to get the difference, however, when I add the Variance row in the matrix table, I get the value of the difference in the Total column. 

How do I make sure to get values as the above table?