Calculating Variance: Year on Year

[prabhupan]

Hello, 

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

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

[Anonymous]

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]

thanks @Anonymous for the response. 

I have multiple IT accounts, so it doesnt work. 

[v-sihou-msft · Accepted solution]

@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]

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

[Anonymous]

Use the Earlier function in DAX, before that sort the year.

Reference: http://community.powerbi.com/t5/Desktop/How-to-subtract-current-row-from-prior-row-and-so-on-in-powe...