Compare data using a given filter

[GeraSanz11]

Hi there!

Im having some trouble with comparing information. I have a data set like this (It's only annual information no months nor days)

Year  | Amount

2015 |  123

2015 |  421

2014 |  321

2014 | 654

2013 | 987

2013 | 876

I want to compare the total Amount per year with the last year BUT using Slicers, so if i choose 2015 in the slicer, it will compare automatically with 2014, and if i choose 2014, it will be compared with 2013 and so on

any advice? 

[TomMartens · Accepted solution]

Hey,

one possible solution could be to create a measure that calculates the amount of previous year like so:

amount PY = 
IF(HASONEFILTER('Table1'[Year]),
 var py = values('Table1'[Year]) -1
 return
 CALCULATE(SUM('Table1'[Amount]),
  'Table1'[Year] = py
 ),
 BLANK()
)

Now you can use both measures in any visualization, if more than one year is selected or NONE, the measure returns a BLANK() / NULL value  

Hope this is what you are looking for

[GeraSanz11]

Thank you so much!

Just what i needed, i don't understand very well the whole formula (the py for example) but for sure is what i was looking for... ill look for more documentation!

THX

[TomMartens]

Hey,

what happens is this

1. Check if just one year is selected in the slicer
   1. if just one year is selected
      1. define a variable py it's just a name for previous year
      2. use values() to return a table with just one column, the special part about values() is this, if the table contains just one row it can be used as a skalar value and it's possible to subtract 1
      3. use the variable to filter the table where the year column equals the variable 
   2. if more than year or none year is selected return blank()