Forum Discussion
Multiple Year over Year calculation
I can't read it, let alone debug it. How about you break the problem into pieces. Write measures for each piece of the problem (interim measures) and stick them in a Matrix so you can see I found they work. Then join them together into 1
Fair point.
The first part I am able to do successfully using TotalYTD but my struggle comes with the second and third piece. What I don't know how to do (and I have tried a lot of things) is how to tell it to pick up specific years going backwards. Below is what I would have thought but I am looking for a solution on what I am doing wrong with the time aspect of the formula.
CALCULATE(SUM('Closed Trades'[Closed GCI]),DATEADD(DateTable[Date],-1,year))
-
CALCULATE(SUM('Closed Trades'[Closed GCI]),DATEADD(DateTable[Date],-2,YEAR)))
/
CALCULATE(SUM('Closed Trades'[Closed GCI]),DATEADD(DateTable[Date],-2,YEAR)
Hey,
here you can find a little example
On the report page "YOY Variations" there is a little table using a measure called "YOY Growth 3Y Trend".
The basic idea is this: create a table with 3 rows from your 3 measures, and then average the values in the column. Each row has the same column, just the calculations are different, in this special case, the number of years looking "behind".
This complete calcutions will look like this:
AVERAGEX( UNION( ROW("YOY Growth", calculation 1), ROW("YOY Growth", calculation 2), ROW("YOY Growth", calculation 3) ) ,[YOY Growth])
If you are relying on intellisense choose the column:Hope this helps
