Calculate Year over Year Variance, Earlier Function Not Working

[Anonymous]

I've literally spent hours trying to figure this out. I've looked at the following posts:

Calculating-Variance-Year-on-Year

How-to-subtract-current-row-from-prior-row-and-so-on-in-power-bi

calculate-variance-in-powerbi-using-dax-query

I've tried countless of variations of the three links above without success. I'm trying to create a year over year variance of the following table:

The following are the expected results:

Thanks in advance for helping me.

[v-juanli-msft · Accepted solution]

Hi @Anonymous

"Earlier Function" can only be used in the calculated column, when you create a measure, you need to use "MAX" function instead.

calculated columns

variance1 =
VAR earliervalue =
    CALCULATE (
        SUM ( Table1[pricipal balance] ),
        FILTER ( ALL ( Table1 ), [year] = EARLIER ( Table1[year] ) - 1 )
    )
RETURN
    IF ( earliervalue <> BLANK (), [pricipal balance] - earliervalue )

variance1% =
VAR earliervalue =
    CALCULATE (
        SUM ( Table1[pricipal balance] ),
        FILTER ( ALL ( Table1 ), [year] = EARLIER ( Table1[year] ) - 1 )
    )
RETURN
    [variance1] / earliervalue

Measures

variance2 =
VAR earliervalue2 =
    CALCULATE (
        SUM ( Table1[pricipal balance] ),
        FILTER ( ALL ( Table1 ), [year] = MAX ( Table1[year] ) - 1 )
    )
RETURN
    IF ( earliervalue2 <> BLANK (), MAX ( [pricipal balance] ) - earliervalue2 )

variance2% =
VAR earliervalue2 =
    CALCULATE (
        SUM ( Table1[pricipal balance] ),
        FILTER ( ALL ( Table1 ), [year] = MAX ( Table1[year] ) - 1 )
    )
RETURN
    [variance2] / earliervalue2

Best Reagrds

Maggie