Forum Discussion

cpunnett's avatar
cpunnett
Helper II
8 years ago

Calculating a variance

I'm trying calculate a variance and need help please.  I'm trying to take submissions from Year 2018 subtract submissions from 2017, then divide that number by submissions from 2017.  My problem is w...
Need Help
danextian's avatar
danextian
Super User
8 years ago

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

 

cpunnett's avatar
cpunnett
Helper II
8 years ago

Thanks danextian, that information got me further down the road.  I can correctly calculate between 2018 and 2017, but I can't seem to figure out the previous years.  I have first measure

2018 Issued3 = 
VAR Issue2018 =
    CALCULATE(SUM('Activity Counts3'[Issued]), 'Activity Counts3'[Year] = 2018)
    RETURN Issue2018

Second measure

Issue Var Custom3 = 
    VAR CurrentYear = SELECTEDVALUE('Activity Counts3'[Year])
    VAR CurrentMonth = SELECTEDVALUE('Activity Counts3'[MonthNumber])
    VAR CurrentProduct = SELECTEDVALUE('Activity Counts3'[Product])
RETURN
CALCULATE([IssuedSum3],
    FILTER(ALL('Activity Counts3'),
        'Activity Counts3'[Year] = CurrentYear - 1 &&
        'Activity Counts3'[MonthNumber] = CurrentMonth &&
        'Activity Counts3'[Product] = CurrentProduct))

Then third

Issue3 Variance = 
VAR Issue2017 = [Issue Var Custom3]
VAR Issue2018 = [2018 Issued3]
VAR IssueSum = CALCULATE(Issue2018 - Issue2017)
RETURN
    DIVIDE (Issue2018 - Issue2017, Issue2017, 0)

This leads me to 

How do I get the 2017 and 2016 calculations?