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 |  32...
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

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