Forum Discussion
YOY Calculation
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 |
-- 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 __yoyBest
Darek
4 Replies
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
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
-- 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 __yoyBest
Darek
