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

Highlighted
Frequent Visitor

Thanks so much!

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

## Helpful resources

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 113 members 1,866 guests
Recent signins:
Please welcome our newest community members: