YOY Calculation

[sentsara]

Hi Team,

Need help on YOY [Year Over Year) % Changes

Declare @YOY table
(
PlanID Varchar(10),
Competitors varchar (100),
Dateofservice int,
RatingYear int,
Rating float
)

insert into @YOY

select 'SC','Mol',2017,2019,3.0 union all
select 'SC','Wel',2017,2019,3.0 union all
select 'SC','BCB',2017,2019,3.5 union all
select 'SC','cen',2017,2019,3.5 union all
select 'SC','amer',2017,2019,4.0 union all
select 'SC','Mol',2016,2018,3.0 union all
select 'SC','Wel',2016,2018,2.5 union all
select 'SC','BCB',2016,2018,3.0 union all
select 'SC','cen',2016,2018,3.0 union all
select 'SC','amer',2016,2018,3.5 union all
select 'SC','Mol',2015,2017,3.0 union all
select 'SC','Wel',2015,2017,3.0 union all
select 'SC','BCB',2015,2017,3.0 union all
select 'SC','cen',2015,2017,3.0 union all
select 'SC','amer',2015,2017,4.0

select * from @YOY

Expected output: 

YOY% =  Rating / PreviousYearRating

PlanID	Competitors	Dateofservice	RatingYear	Rating	PreviousYearRating	PreviousYearRating	YOY%
SC	Mol	2017	2019	3	2018	3
SC	Wel	2017	2019	3	2018	2.5
SC	BCB	2017	2019	3.5	2018	3
SC	cen	2017	2019	3.5	2018	3
SC	amer	2017	2019	4	2018	3.5
SC	Mol	2016	2018	3	2017	3
SC	Wel	2016	2018	2.5	2017	3
SC	BCB	2016	2018	3	2017	3
SC	cen	2016	2018	3	2017	3
SC	amer	2016	2018	3.5	2017	4
SC	Mol	2015	2017	3	2016	0
SC	Wel	2015	2017	3	2016	0
SC	BCB	2015	2017	3	2016	0
SC	cen	2015	2017	3	2016	0
SC	amer	2015	2017	4	2016	0

[Anonymous]

Mate, what's the definition of YOY%? The very first line in your data set states that YOY% is 1.000 but I can see that the rating goes from 3 to 3. How the heck then is it possible that the relative increase is 1?

The other issue is... What is it that you want? Is it a measure? A column? What's the input data? Mate, please do yourself a favor and be more descriptive.

Thanks. 

Best

Darek

[sentsara]

Updated my content.

i need to bring two columns value

PreviousYearRating for the same competitors what is the rating achieved and find the Year over Year (%) changes

[Anonymous · Accepted solution]

-- Assumptions:
-- T is the table
-- RatingYear is integer

-- Questions:
-- What's the use of PlanID if in the
-- sample data set it's all the same?

-- calculated column
[PreviousYearRating] =
var __currentYear = T[RatingYear]
var __prevYear = __currentYear - 1
var __currentCompetitor = T[Competitors]
-- If this calculation is slow, then one has
-- to use an alternative that will not use
-- CALCULATE but only FILTER on its own.
var __prevYearRating =

     CALCULATE(
        VALUES( T[Rating] ),
        T[Competitors] = __currentCompetitor,
        T[RatingYear] = __prevYear,
        ALL ( T )
    )
return
	__prevYearRating
	
-- calculated column
-- Please do not multiply the output by 100.
-- Use formatting to format the number correctly as percentage.
[YOY%] =
var __currentRating = T[Rating]
var __prevYearRating = T[PreviousYearRating]
var __yoy = DIVIDE( __currentRating, __prevYearRating )
return
	__yoy

Best

Darek

[sentsara]

Thank you so much darlove. its working