cancel
Showing results for
Did you mean:
bcobrien77 Frequent Visitor

## Year over Year Change only for Custmoers with two years of data

Hi,

I am trying to use either the quick measure year over year change or create a standalone measure to calculate the year over year change of income.  My problem is that the results are skewed because the customer list changes from year to year with new customers coming on, some customers leaving and some staying the same.

I have looked at the other posts and it feels like this should be fairly simple.

In this example, I would only calculate this for Customers A, B and C becaue they had two years of data.  D and E would be exculded.

What is the best way to accomplish this in Desktop?

 Customer Year Value Desired Result A 2014 10 B 2014 15 C 2014 20 D 2014 25 A 2015 30 200% B 2015 35 133% C 2015 40 100% E 2015 45 0 Average 144%

Thanks

4 REPLIES 4
Highlighted Community Support Team

## Re: Year over Year Change only for Custmoers with two years of data

First create a calculated column in the table
```Column =
CALCULATE (
MAX ( Table1[Value] ),
FILTER (
Table1,
Table1[Customer] = EARLIER ( Table1[Customer] )
&& Table1[Year]
= EARLIER ( Table1[Year] ) - 1
)
)``` Then create measures in the table visual
```Measure =
DIVIDE ( MAX ( [Value] ) - MAX ( [Column 3] ), MAX ( [Column 3] ) )```
```Measure 4 =
IF (
HASONEVALUE ( Table1[Column1] ),
CALCULATE (
DIVIDE ( MAX ( [Value] ) - MAX ( [Column1] ), MAX ( [Column1] ) ),
FILTER ( Table1, [Column1] <> BLANK () )
),
SUMX ( Table1, [Measure] ) / 3
)``` Best Regards
Maggie

bcobrien77 Frequent Visitor

## Re: Year over Year Change only for Custmoers with two years of data

Thanks so much!

bcobrien77 Frequent Visitor

## Re: Year over Year Change only for Custmoers with two years of data

One more thing.

If you did not how many rows (customers) to divide by in the final measure, how you would calculate that?

As I scale this up to 1000's of rows (with many of them being ignored due to a lack of 2 years of data), this will get more confusing to calculate.

Thanks Community Support Team

## Re: Year over Year Change only for Custmoers with two years of data

You can use "calculate" and "count" to calculate how many rows need to be divided by the final measure.

This formula will depend on the specific dataset.

Best Regards

Maggie