Forum Discussion

GeraSanz11's avatar
GeraSanz11
Frequent Visitor
9 years ago
Solved

Compare data using a given filter

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? 

Need Help
  • TomMartens's avatar
    TomMartens
    9 years ago

    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

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    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's avatar
      GeraSanz11
      Frequent Visitor
      9 years ago

      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's avatar
        TomMartens
        Super User
        9 years ago

        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()