cancel
Showing results for
Did you mean:
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

Super User IV

@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/

Proud to be a Super User!

Frequent Visitor

@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?

Announcements