Calculate % difference between two years

Question

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	Active/Inactive Sale	Deparment
2020	Active	Montevideo
2019	Active	Montevideo
2020	Inactive	Salto
2020	Active	Paysandu
2019	Inactive	Salto
2019	Active	Montevideo
2020	Inactive	Canelones
2019	Active	Canelones

The column Year is a integer number not a date value.
I need to calculate 2023 vs 2022, vs 2021 vs 2020.

	Montevideo			Salto
Active/Inactive Sale	2019	2020	% change	2019	2020	% change
Active	23%	19%	-4%	22%	16%	-6%
Inactive	22%	19%	-3%	13%	21%	8%
Mary	24%	22%	-2%	12%	26%	14%

I used this measure to calculate the chosen selection of the slicer

Spoiler

Measure =

var _sliceryear = CONCATENATEX(DISTINCT('Cartera Total'[yearslicer]),[yearslicer])

var _converts = CONVERT(_sliceryear,INTEGER)

var _lastyear = CALCULATE(COUNTA('Cartera Total'[system_no]),FILTER(ALL('Cartera Total'),'Cartera Total'[FY Año Alta]=_converts-1 && 'Total Sales'[Active/Inactive]="Inactive"))

return _lastyear

Measure = var _sliceryear = CONCATENATEX(DISTINCT('Cartera Total'[yearslicer]),[yearslicer]) var _converts = CONVERT(_sliceryear,INTEGER)var _lastyear = CALCULATE(COUNTA('Cartera Total'[system_no]),FILTER(ALL('Cartera Total'),'Cartera Total'[FY Año Alta]=_converts-1 && 'Total Sales'[Active/Inactive]="Inactive"))return _lastyear

any suggest?

some_bih · Answer

Hi&nbsp;jfern78&nbsp;I would recommend you to check link below, part Year-over-year growth, as this is one of the best practice. This solution require Calendar / Date table (and your data from Fact table as date type), but article is worth reading and implementation for your case.&nbsp;Hope this help, kudos appreciated.&nbsp;https://www.daxpatterns.com/standard-time-related-calculations/&nbsp;

jfern78 · Answer

Thanks, i will see it.&nbsp;i'm figuring out how to do it.

jfern78 · Answer

i made a column with correct FY Year/date, but when i try to calculate last year sales return me blank.

Total Sales Last Year = CALCULATE([Total Cartera Activa],dateadd('Cartera Total'[Fiscal Date],-1,Year))

some_bih · Answer

Hi&nbsp;jfern78&nbsp;use Date / Calendart table to "leverage" proper usage of Power BI time intelligence functions like YTD, MTD... do not put fact table year column in visual.

Forum Discussion

Calculate % difference between two years

4 Replies

Related content

Dynamic Calculation Different Period

Different Calculations

1. How to add two different calculated columns of two different table

Calculating Differences

Calculating day/time difference