cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sammy_01
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:

 20182017201620152014
Sales350300275260255
var 5025155

 

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

1 ACCEPTED SOLUTION
Icey
Community Support
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.

View solution in original post

3 REPLIES 3
Icey
Community Support
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.

View solution in original post

amitchandak
Super User IV
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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.