Forum Discussion
Solved
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 @YO...
-- 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
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
Thank you so much darlove. its working