Calculate Variance of Sum By Row Criteria Over Previous Year

[Anonymous]

Hi guys, not quite sure how to word this the best, but I am trying to create a measure that will allow me to display the variance for any of the accounts, over the previous year.  For example what would be the variance in year 2017 for account 1, from year 2016, etc.

Here is some sample data:

2015	Act 1	$10
2015	Act 2	$31
2016	Act 1	$21
2016	Act 2	$21
2017	Act 1	$12

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can use this measure

variance = 
VAR _year = MAX(SampleTable[Year])
VAR _thisYear = SUM(SampleTable[Price])
VAR _previousYear = CALCULATE(SUM(SampleTable[Price]),ALLEXCEPT(SampleTable,SampleTable[Account]),SampleTable[Year] = _year - 1)
RETURN
_thisYear - _previousYear

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.