Excel Slicers for rolling periods, for example; the last 12 months, requires a column in your source data that looks up the date and compares it to today’s date to see if it falls into the current period.
Download the Workbook
Watch the Video
Watch right to the end to see the bloopers!
Creating Excel Slicers for Rolling Periods
Below is my data: in column A I have the date for each record and in column C I use an IF function to compare today’s date (returned from your computer’s clock using the TODAY() function in cell F2), to the date in column A to see if it falls within the last 12 months.
=IF(today-[@Date]<365,"Last 12 Mths",">12 Mths")
Note: today is a named range for cell F2 which contains the formula =TODAY(). This formula returns the date from your computer’s clock.
English Translation of the IF formula:
=IF (today’s date minus [the date in column A] ) is less than 365, then enter the text "Last 12 Mths", otherwise enter the text ">12 Mths"
Now we can build the PivotTable and or Pivot Chart and add a Slicer for the Rolling Period:
And because I’ve grouped my data by month I don’t have the day detail so I want to make it absolutely clear which period the chart is displaying by inserting a custom chart title that automatically displays the date range.
Warning: don’t forget to refresh the PivotTable/Pivot Chart so that it displays the correct ‘last 12 months’.
If your PivotTable is pulling in the data from another system you could turn on the ‘Refresh data when opening the file’ in the PivotTable Options menu:
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.