Multiple Year over Year calculation

[JasonP]

Hi all,

I am trying to calculate a 3 year average of year over year growth. What I am looking for is to calculate the current YTD (2017) vs last years ytd (2016), then 2016 full year to 2015 full year and lastly 2015 full year to 2014 full year. Basically, the idea is to understand if an office is trending upward in sales, downward, flat, etc across multiple metrics.

Below is my formula which isn't working. Any suggestions? This will be used in a table with other values using this same formula and others. 

YoY GCI = (((TOTALYTD(SUM('Closed Trades'[Closed GCI]),DateTable[Date])-TOTALYTD(SUM('Closed Trades'[Closed GCI]),DATEADD(DateTable[Date],-1,YEAR)))/ TOTALYTD(SUM('Closed Trades'[Closed GCI]),DATEADD(DateTable[Date],-1,YEAR)))

+((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)))

+((CALCULATE(SUM('Closed Trades'[Closed GCI]),DATEADD(DateTable[Date],-2,YEAR))-CALCULATE(SUM('Closed Trades'[Closed GCI]),DATEADD(DateTable[Date],-3,YEAR)))/ CALCULATE(SUM('Closed Trades'[Closed GCI]),DATEADD(DateTable[Date],-3,YEAR))))/3

[MattAllington]

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 

[JasonP]

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)

[TomMartens]

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