It is possible to create a filter that filters what you see without losing all the data - lookup is the right formula for that. But no idea if it works properly in your setup
I've attached an example using superstore to add running sum of sales while filtering on dates.
I've used a Lookup on Date to filter, so the date must be somewhere in the view to make sure I have all of them.
Hope it makes sense.
FilterByDate_WhileKeepingTotal.zip 935.1 KB
Awesome! Thanks Dana. Great example. I now have a bar chart controlled by the filter and a line which shows the running total from period 1
One thing I've notice Dana... Although I have the same filter on three different worksheets, they aren't linked. On the dashboard, changing the filter only affects one of the charts. Is there a way to link that filter to all three charts?