3 Replies Latest reply on Oct 12, 2017 2:09 PM by Justin Larson

    Year on year tiles which take account of current filter selections

    Guy Morgan

      I really need help with something that I think should be simple but which doesn't seem to be working for me!


      All I want is to display a single % figure for year-on-year change which dynamically updates based on the month and also any combination of filters that are applied in a dashboard view.


      I can get a single figure to display for the primary metric - but trying to create a table calculation doesn't work because it doesn't display.


      What I want is - a big number for the sessions metric - a big number for the year on year change *for whatever filters are currently applied*.


      Tearing my hair out - any help appreciated! Here's a packaged workbook with dummy data...

        • 1. Re: Year on year tiles which take account of current filter selections
          Justin Larson

          The problem here is that you are filtering out the prior year by use of the MY(Date) filter, so the YoY calculation has no prior year value to lookup.


          One way to handle this is to create a parameter and fill it with the dates you want available for a user to choose. Then write each of your measures as (pseudo code here):

          IF MY(Date) = [Parameter MYDate] then [Measure] END


          This results in a new virtual column that only holds values where the Date is the selected. That way you don't need to use the filter, you can choose a parameter which effectively nulls out all dates not chosen, leaving you free to use your existing window function which lookups the prior year's value.


          With a simple calculation like:

          MY(Date) = [Parameter MYDate]


          you can drop that on filter for some sheets, and rows on others, hiding "FALSE" rather than filtering it out.


          Aside from having to rewrite your measures (not a huge obstacle with this sample, but may be more complex with your real workbook) - the biggest drawback of this approach is that you have to populate the parameter with values. If you refresh the data, and the relevant dates change, you have to go into the parameter and update it. This also does not allow for any multi-select scenarios, where a regular filter has no problem with.


          Hope that's descriptive enough without providing a working example.

          1 of 1 people found this helpful
          • 2. Re: Year on year tiles which take account of current filter selections
            Guy Morgan

            Thanks so much for this - it's got me nearly there. I now have a problem where I can't display just the % change number - I can create a table which looks across the whole dataset and only returns the YOY calculation for the given month - but what I really want is to just display the % figure, not a whole table. Same would go for year on year difference, etc.


            I've updated the workbook - I feel like what I need to do is use a Filter formula on the MonthYear field, where if YOY <> NULL then I show the row, but if it is NULL then I don't - but IF and IIF statements seem to reject YOY as a valid measure. I've updated the workbook - any advice on filtering table rows appreciated!!

            • 3. Re: Year on year tiles which take account of current filter selections
              Justin Larson

              so, here's a different approach at performing the YoY calculation without filtering. what I've done is taken the date value, passed it through a window function that returns the same date. since window functions are evaluated before filtering, when I drop this on the filter, I still get the result of the YoY calc, but successfully get rid of the other rows. This approach actually works without the parameter, except in this case I compared to parameter value to return true or false on if the date matches the selected parameter value.


              See attached on yoy tab.