I have a several workbooks that I update monthly, and each month have to change the YTD anchor date in multiple locations which is somewhat time consuming.
Is it possible to have a calculated field that would query YTD and dynamically set the anchor for the last day of the previous month?
If so I would greatly appreciate some insight or an example on how to accomplish this.
Last day of previous month can be calculated by something like:
You could then create a filter for YTD to that point and just put this on every sheet. This would be something like
[DateField] <= DATETRUNC('month', [DateField]) - 1
Place this on your filters shelf, and keep true values only.