1 of 1 people found this helpful
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.
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!!
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.