I have been searching for a viable solution to update a calc. field inside a pivot table and can't seem to find a solution.
Here is why Im looking for a way to update the calculated field using a formula or other method
Currently I have to manually update the calculated field "Report Month" to =5 if in a May reporting month. As there several pivots not all using same data source ( diff access tables) I have to update SEVERAL "Report Month" calculated fields in SEVERAL pivots. "Report Month" is used as a denominator in all other calculated fields in the pivots (all annualized % calc field use this field)
I found vba code that will delete all calculated fields and then add them back in with report month updated but not much more automated and found that this didnot always work as expected - current process more effiectent.
You are unable to use date/time related syntax in pivot calc field ie Now() Today() EOMonth() etc.
And if I add a Report Month field in the data source (RM) say equal to 5 it will not work. Seems to sum the records used in calculation even if I use max() or First() in the calc field syntax "Report Month" = Max('RM')
Any idea's or am I stuck updating manually?
Example of Report Month being used:
'YTD' * (12/'Report Month') / 'Baseline'
Thank you for taking the time to help!!!
Here is why Im looking for a way to update the calculated field using a formula or other method
Currently I have to manually update the calculated field "Report Month" to =5 if in a May reporting month. As there several pivots not all using same data source ( diff access tables) I have to update SEVERAL "Report Month" calculated fields in SEVERAL pivots. "Report Month" is used as a denominator in all other calculated fields in the pivots (all annualized % calc field use this field)
I found vba code that will delete all calculated fields and then add them back in with report month updated but not much more automated and found that this didnot always work as expected - current process more effiectent.
You are unable to use date/time related syntax in pivot calc field ie Now() Today() EOMonth() etc.
And if I add a Report Month field in the data source (RM) say equal to 5 it will not work. Seems to sum the records used in calculation even if I use max() or First() in the calc field syntax "Report Month" = Max('RM')
Any idea's or am I stuck updating manually?
Example of Report Month being used:
'YTD' * (12/'Report Month') / 'Baseline'
Thank you for taking the time to help!!!