Hi Mynda,
Thanks to your idea, I now can automatically categorize (to five weeks, ending with the derived date) my source data based on the Timeline selection. Please help me reflect this in a chart, based on a Pivot of this categorization. I'm not sure if I conveyed my requirement properly, so please find attached the file I made. (I deleted the 'Data' sheet to bring the file size down.)
What I want - is the 'Trend' for the last five weeks, to update in correspondence with the Timeline. Currently it changes only if the user does a 'Refresh All'. FYI - the two 'simulations' are our estimates of how much we would actually end up with, owing to the current market (Corona / Oil price) conditions. We are putting together two contingencies in place; the trend would help us determine which one to activate.
Appreciate if you would also suggest ways to improve the 'Dashboard' overall.
Many thanks,
Thomas
Hi Thomas,
I would abandon the Timeline Slicer and use a technique more like this Slicers for rolling periods technique. You can replace 'today's' date in this technique with a drop down list that allows the user to choose the date/month, which is then linked to the formula that calculates the periods.
You wouldn't need the Slicer itself, but it still requires a refresh of the PivotTable. You could trigger this when the Slicer selection or the drop down list is changed.
I hope that gives you some ideas.
Mynda
