Calculate % difference between two years

[SuperCal99]

I would like to work out % difference between 2 years for a set of values and having some trouble doing so.

First of all, let’s say I have the following sample of data

Year	Fruit	Name
2020	Apple	John
2019	Apple	Mary
2020	Banana	Paul
2020	Orange	John
2019	Apple	Mary
2019	Banana	John
2020	Lemon	Paul
2019	Apple	Paul

In a visual, I want to create the following Matrix.

	Apple			Banana
Name	2019	2020	% change	2019	2020	% change
John	23%	19%	-4%	22%	16%	-6%
Paul	22%	19%	-3%	13%	21%	8%
Mary	24%	22%	-2%	12%	26%	14%

The year % is easy, im just struggling to correctly work out the values in the  % change for each person using a measure

Any assistance would be greatly appreciated

Regards,

Cal

[Anonymous]

Hi  @SuperCal99 ,

You can refer to the following link to try out grouping in a custom matrix:

 https://community.powerbi.com/t5/Desktop/Matrix-column-header-label-custom-group/td-p/1253196

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@SuperCal99 , You need create a separate year table(say Date) and the create a measure like

example

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

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

[SuperCal99]

Thanks @amitchandak - appreciate that