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,

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