Forum Discussion

Helper I

9 years ago

Solved

Calculating Variance: Year on Year

Hello,

How do i calculate the variance between the IT spend year on year?

I need help with Variance type 1 & 2 given below.

Tips and Tricks

v-sihou-msft
9 years ago
prabhupan

To calculate the variance between current year and previous year for each account, you can create a calculated column and use EARLIER() function to get the previous year context for calculation. Please refer to formula below:

Variance = var PreviousYearType1=CALCULATE(SUM(Table1[Type1]),FILTER(Table1,Table1[Account]=EARLIER(Table1[Account]) && Table1[Year]=EARLIER(Table1[Year])-1)) return IF(PreviousYearType1=BLANK(),BLANK(),Table1[Type1]-PreviousYearType1)

Regards,

5 Replies

Anonymous
Not applicable
9 years ago
Hi

Try the below approach.

Add 3 additional column
***Replace Table3 with your table name

Rank = RANKX(Table3,Table3[Year],,1)
PreviousType1 = LOOKUPVALUE(Table3[Type1],Table3[Rank],Table3[Rank]-1)
PreviousType2 = LOOKUPVALUE(Table3[Type2],Table3[Rank],Table3[Rank]-1)

VarianceType1 = IF(ISBLANK(Table3[PreviousType1]),BLANK(),Table3[Type1]-Table3[PreviousType1])

VarianceType2 = IF(ISBLANK(Table3[PreviousType2]),BLANK(),Table3[Type2]-Table3[PreviousType2])
- prabhupan
  Helper I
  9 years ago
  thanks Anonymous for the response.
  
  I have multiple IT accounts, so it doesnt work.
  - v-sihou-msft
    Microsoft Employee
    9 years ago
    prabhupan
    
    To calculate the variance between current year and previous year for each account, you can create a calculated column and use EARLIER() function to get the previous year context for calculation. Please refer to formula below:
    
    Variance = var PreviousYearType1=CALCULATE(SUM(Table1[Type1]),FILTER(Table1,Table1[Account]=EARLIER(Table1[Account]) && Table1[Year]=EARLIER(Table1[Year])-1)) return IF(PreviousYearType1=BLANK(),BLANK(),Table1[Type1]-PreviousYearType1)
    
    Regards,