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

[bcobrien77]

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

[v-juanli-msft]

Hi @bcobrien77

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]

Thanks so much!

[bcobrien77]

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

[v-juanli-msft]

Hi @bcobrien77

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