cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bcobrien77 Frequent Visitor
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?

 

CustomerYearValueDesired Result
A201410 
B201415 
C201420 
D201425 
A201530200%
B201535133%
C201540100%
E2015450
    
  Average144%

Thanks

4 REPLIES 4
Highlighted
Community Support Team
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
    )
)
2.png
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
)
3.png
 
Best Regards
Maggie
 
bcobrien77 Frequent Visitor
Frequent Visitor

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

Thanks so much!

bcobrien77 Frequent Visitor
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
Community Support Team

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

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