Forum Discussion
Calculating a variance
I did follow your formula. Which is where [Issue 2017] and [Issue 2018] came from. I created those measures just from those specific years.
If you want to get the difference between 2018 and 2017 and since you have already created external measures, you could simply write Variance = [2018 Issued] - [2017 Issued]
If you placed the measure you just created in a pivot table and divide them into columns of years, this is what happens
- 2018 = [2018 Issued] - [2017 Issued] > This returns the value of [2018 issued] since this column is for 2018. What's visible in the current filter context is just 2018. 2017 is blank. So [2018 Issued] - BLANK() = [2018 Issued]
- 2017 = [2018 Issued] - [2017 Issued] > This returns a blank value for [2018 issued] since this column is for 2017 . What's visible in the current filter context is just 2017. So BLANK() - [2017 Issued] = negative of whatever the 2017 value is.
I think what you are trying to achieve is the year over year difference and not simply the difference between those two years. If so, this would have involved a Time Intelligence function if you had a standard data column but still a workaround is possible.
Here's the modified measure to be formatted as percentage
Variance YoY% =
VAR YEAR_ =
SELECTEDVALUE ( 'Table'[Year] )
VAR LY =
CALCULATE ( [Sum of Issued], ALL ( 'Table'[Year] ), 'Table'[Year] = YEAR_ - 1 )
RETURN
DIVIDE ( [Sum of Issued] - LY, LY )
A similar tutorial can be found on youtube along with standard time intelligence calculations. https://www.youtube.com/watch?v=g_3eLaKgeEQ