Change Pivot Table Fields Month by Month Automatically

It's difficult to answer questions about pivot tables without seeing the source data. However, I have a suggestion that might work for you and that is to take care of the matter on the source data side.

First of all, since you have Excel 2016, you should have the data in an Excel table. Two of the advantages of Excel Tables that apply here are: 1. They know how big they are, so all charts, pivot tables and formulas that reference them are always correct regardless of the amount of data in the table and 2. They "remember" and automatically copy down formulas, formats, validations, etc.

So, you can add a helper column that can compute "This Month" and "Last Month" based on the current date.

Beyond this, I can't recommend anything without seeing a sample workbook.

Here is more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel