Dear, is it possible to let the filter be equal to a calculated field? So instead of manually change the filter content, it can be changed automatically based on another calculated filter, e.g. current month? Thanks!
Typically you will use the filter in the value field measures e.g. CALCULATE(expression, filter)
If this doesn't work for you, please provide a sample file that illustrates your scenario.
Mynda
Thank you for your kind reply!
I am not sure if I understand you correctly but I didn't mean to filter out values itself on the result of the pivot table but the filter needs to be equal to a calculated field.
E.g. in the screenshot below, the filter month right now is December as the last closing month of December. I would want it to be automatically changed to January now as the current closing month is January. This can be easily defined by a excel calculation but I can't do that in the pivot table filter.
So I thought if I can let the filter month be equal to another excel cell where the month changes every time, then my problem will be solved?
Thanks!
You can't put a measure in the filter field. If you want a filter that is 'current month' then you'd have to add a column to your source table that classifies each date into 'current month' and 'past months' with a formula that checks if the month is current e.g.:
=IF(MONTH(date column)=MONTH(TODAY()),'Current Month', "Past Months'
Then you can use this field in your filter.
Mynda
