cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JasonP Frequent Visitor
Frequent Visitor

Multiple Year over Year calculation

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

 

3 REPLIES 3
Super User
Super User

Re: 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 

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
JasonP Frequent Visitor
Frequent Visitor

Re: Multiple Year over Year calculation

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)

Super User
Super User

Re: Multiple Year over Year calculation

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:

YOY Growth 3years.png

Hope this helps

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!