Forum Discussion

prabhupan's avatar
prabhupan
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.           
Need Help
Tips and Tricks
  • v-sihou-msft's avatar
    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,

Anonymous's avatar
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's avatar
prabhupan
Helper I
9 years ago

thanks Anonymous for the response. 

 

I have multiple IT accounts, so it doesnt work. 

 

  • v-sihou-msft's avatar
    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,

    • sagarika2017's avatar
      sagarika2017
      Regular Visitor
      9 years ago

      so i have this data in which one column is year and the other column is sales

       

      Year        Sales

      2014        332332332

      2015         23223232
      2016        2132321332

      2017        219389323

       

      now i have to calculate the rate of change of the sales from one year to the other

       

      the data type for year is Number

      sales is revenue

       

      how do i do it