Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate Year over Year Variance, Earlier Function Not Working

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.

Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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