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 calc...
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

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