I've a problem when using the filter function in a measure
The line of code below works fine but as you can see, the year is hard coded.
=CALCULATE(SUM(Dashboard[Sales]), year(Dashboard[InvDate]) = 2016)
But when I use the filter function, 2015 and 2016's data is included 
=CALCULATE(SUM(Dashboard[Sales]), FILTER(Dashboard, YEAR(Dashboard[InvDate] = Calendar[This Year] ) ) )
I've of course checked Calendar[This Year] and this equals 2016.
Any clues as to what is wrong?
Cheers,
Martin
Hi Martin,
Thanks for using the forum to post your question. I've moved it to the Power Pivot course members group. Please use this for questions relating to the course going forward.
On to your question; presumably in the column called 'This Year' you have all years represented, i.e. you have rows with 2015 and 2016, and possibly others? If so then your filter criteria of =Calendar[This Year] isn't filtering at all since that column contains more than just 2016.
I know you said "I’ve of course checked Calendar[This Year] and this equals 2016" but it seems unlikely to me that you would have one column in your Calendar table that contains 2016 on every row when your model also contains data for 2015.
You only need to use FILTER if you want to override the filters in context. If your PivotTable has the year as a row/column field then a simple SUM would do:
=SUM(Dashboard[Sales])
Then you can allow the PivotTable context to do the filtering. Just as you would with a regular PivotTable.
If that doesn't clarify things then perhaps some screenshots of the PivotTable structure and model diagram view would help us understand what you're hoping to achieve.
Mynda
Hi Mynda!
A few points that I forgot to mention:
=Calendar[This Year] is based on =MAX(Calendar[YearKey]) and this has the value of 2016
=Calendar[Last Year] is based on =MAX(Calendar[YearKey]) - 1 and this has the value of 2015
A component of my Dashboard is to show this years sales v last years sales. e.g.
Month TY LY
Jan 100 95
Feb 120 115
March 105 100
Apri 120 110
etc.
So for this particular part of the dashboard, I don't want the pivot table to change if a date filter or slicer is applied.
Cheers,
Martin
Hi Martin,
Thanks for clarifying.
When you reference a measure in a formula you don't have to explicitly reference it. That is to say you don't have to prefix it with the Table name. To do so implies that you're referencing a Table and column as opposed to just a measure. Measures are typically referenced with just their name, no column prefix. Hence my confusion.
Try this:
=CALCULATE(SUM(Dashboard[Sales]), FILTER( Dashboard, YEAR(Dashboard[InvDate]) = [This Year] ) )
If that doesn't work then I'll need to see your model diagram.
Mynda
