Can you use filters in Calculated Fields?
I have a pivot table showing forecast monthly fee income for each project we're working on over the current financial year. I've been asked if we can add two columns to this table:
1. To show the total fees for each project previous to the current fianancial year
2. To show the total fees for each project forecast for future financial years
The current pivot table has a date filter set to 'Current Financial Year', but users are free to alter this to different time periods to suit their reporting needs.
For this reason, I think I need to create a calculated field to populate the two new columns. In the calculated field, I would need to reference the current date filter setting.
Can you use filters in calculated fields to achieve something like: SUM ('Fees') WHERE 'Fee_Date' NOT IN 'Date_Filter_Value'
In the example above 'Date_Filter_Value' could equal [Current Financial Year] or [Last Quarter] or some other time period.
I can't see a way to reference filters in calculated fields. Is there a way to do this or perhaps some other way to achieve my goal?