cancel
Showing results for
Did you mean:
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.

3 REPLIES 3
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.
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.

-

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:

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!