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?
Column = CALCULATE ( MAX ( Table1[Value] ), FILTER ( Table1, Table1[Customer] = EARLIER ( Table1[Customer] ) && Table1[Year] = EARLIER ( Table1[Year] ) - 1 ) )
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 )
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.
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.