Filtering Pivot Table - use relative position in list?

[Bogbog83]

I produce a weekly report that is based on multiple rolling 3 week periods.

This uses several pivot tables based on 7 weeks of raw data, where each table filters out either the most recent 3 weeks, or 1 week back, or 1 week back, and so on. Each week, on the raw data table, I add the most recent week of data and delete the oldest - and that means I have to update my filters each week as the weeknumbers change in the raw data.

For example, in the screenshot, this filter included 25/26/27 last week when those were the most recent weeks. Now I've added week 28, I want the filter to include the 3 bottom selections.

What I'd like to achieve is this filter to always select the bottom 3 values, the table next to it to select the 4th/5th/6th, the next one to select the 3rd/4th/5th, and so on but I can't come up with thing to achieve that.



Any help to achieve that will be enormously appreciated.

 

[johnny51981]

Short of moving all of this processing to Power Query, I would probably do the following:

1. Create a table that contains all of the Reporting Week numbers that you would like to filtered for.
2. Add a helper column to your raw data that says, if the WeekNumber is in the table, then "Yes", otherwise "No"...or some kind of flag that might mean something more to you.
3. Add the helper column as a Filter to your PivotTable, and set the filter to the "Yes" flag (or whatever you created in the previous step).

Then, as a part of your regular update process, you would just
1. Add your new data.
2. Update the Reporting Weeks table with the current targeted 3 WeekNumbers.
3. Refresh PivotTable. The correct weeks and associated summary will display without having to update a filter.