Time-Series PivotCharts with Dynamic Date-Axis / by Robert Walker

A while back I wrote a post about how you could create date/time-accurate time-series charts using Excel PivotTables and PivotCharts, which I find extremely helpful given all of the power embedded into a PivotTable.  However, a colleague of mine recently gave me a lot of time-series data and wanted to filter to only specific locations.  It so happened that not all of these data recorder locations came online at the same time (as is usually the case), but he really liked the power the PivotCharts provided with slicers, timelines, auto-titles, data inspector, etc.  However, he didn't like that the Date-axis was essentially static and showed all time (in this case a couple of decades) when some of his instruments were only a few years of data.  Here's an example of what I mean:

Setup Your Own Example

Prerequisites:  Excel 2013 with PowerQuery enabled or Excel 2016 (I'm using Excel 2016 so some instructions may differ slightly); beginner VBA skills (or ability to lookup how to use VBA in Excel).

If you don't want to go through all of this you can download my copy.

  1. Create a blank Excel Workbook.  Save it as a Macro-Enabled Excel Workbook (.xlsm)
  2. Create some fake data to use in our example:
    1. In A1 type "Location", B1 type "Date", and C1 type "Reading"
    2. In A2 type "Location 1"
    3. In B2 type your start date - I used 1/1/1990
    4. in B3 type the formula "=RANDBETWEEN(0,100)" (or whatever data range you want)
    5. In B3 type the formula "=B2+RANDBETWEEN(1,100)"
    6. Copy cell B3 down about 200 rows.
    7. Copy Cell A2 and C2 down to where column B ends.
    8. You should now have "Location 1" in all of Column A, a series of dates in Column B, and some data in Column C.
    9. Now copy A2 to end of column C (everything but your headers) and paste below your dataset.  Repeat as many times as you like.  Update the "Location" so they are unique for each set you pasted (e.g., Location 2, 3... Location n).
    10. On your last location, remove all dates before 2015.
  3. Copy all of your data and past as values to prevent it from changing.
  4. Convert your data to a table (Ctl+T).
  5. On the data-tab in the Excel Ribbon, in the "Get & Transform" section, click "From Table" and walk through the steps to create a new query.
  6. In the Query Editor, click the down arrow on "Close & Load".  Choose "Connection Only" and "Add Query to Data Model"
  7. Also under the Data tab, click "Manage Data Model".
  8. In the Data Model (aka Power Pivot), click the "Design" tab.  Click "Date Table" > "New"
  9. Also on the Design tab, click "Create Relationship".  Choose your table and the date column in the upper half and choose the calendar and the date column in the lower half.
  10. Save and close the Date Model window.
  11. On the "Insert" tab of Excel, click the down arrow under "PivotChart" and select "PivotChart and PivotTable".
  12. Choose "Use this workbook's Data Model" and "New Worksheet"
  13. Configure your new PivotChart to use Date from the calendar on the Axis, Location from your table on the Legend, and Reading from your Table as the Values.
  14. Change the chart type to your liking (I prefer Line with Markers for Time-Series)
  15. Adjust whatever other design elements you like (legend, titles, etc.)
  16. Right-click on the PivotTable behind the PivotChart and choose "PivotTable Options"
  17. On the "Display" tab, turn on "Show items with no data on rows".  This will make it so that all dates are shown for the entire dataset so that the PivotChart date-axis is accurate.
  18. Add a slicer to select for location (with the PivotChart selected > Analyze tab > Insert Slicer > Check Location).  Play with the slicer.  You should see that the date-axis is static and covers the entire date range.  This will make your last location look strange because it only covers a small part of the chart (see images above).

That's it for setting up the example.  If you don't want to go through all of those steps you can download my Excel copy (note: this is not a Macro-Enabled file for security reasons).

Dynamic Date-Axis

Ordinarily, PivotCharts would automatically adjust the axes based on the filter, but because of the Date-Axis requirement and our setting of always showing blank rows (in order to achieve the correct date spacing), that doesn't work.  As a result, we need to use some "onUpdate" VBA code to detect a change in the PivotChart source PivotTable and filter the date accordingly.  Here is the VBA code with in-line comment explanation:

'VBA Subroutine to find the date of the first data point in a pivot table and use it to filter for a pivot chart.
' sh is the worksheet with the pivottable/pivotchart you want to apply this code to.
Sub DateFilter(sh As Worksheet)

'Disable event firing to prevent re-trigger during filtering
Application.EnableEvents = False

Dim r As Long 'This will store the row of the first data point
Dim d As String 'This will store the date of the first data point

sh.Activate 'Activate our Sheet just in-case the focus was changed
' Clear any existing filters on the Date column in our PivotTable
' "PivotTable1" is the name of our PivotTable in the worksheet
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Calendar].[Date].[Date]"). _
        ClearAllFilters
' Select range to search
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataOnly, True
' Find first data point and get its row number
r = Selection.Find(What:="*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Row
'Get Date to use in filter; assumes that your date is in column A
d = sh.Cells(r, "A")
d = Format(d, "M/1/YYYY") ' Format the date to the first of the month
sh.Cells(1, 1).Select ' Move cursor back to A1 (visual for user)
'Filter Data based on date   
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Calendar].[Date].[Date]"). _
    PivotFilters.Add2 Type:=xlAfterOrEqualTo, Value1:=d, WholeDayFilter:=False

'Reenable events
Application.EnableEvents = True
End Sub

How to Use The Code

Add this code to a new module in your workbook.  You can call this code anytime and pass in the worksheet that contains the PivotTable and PivotChart you want modified, but the best use of this is to make it run automatically when a user changes the slicer selection:

In the VBA Editor (ALT+F11) choose the worksheet that has the PivotTable and PivotChart in it ("Pivots" if you're using my file).  At the top, change scope to "Worksheet" (i.e., where it says "(General)" change to "Worksheet").  Then for the action (default is "SelectionChange"), set it to "PivotTableUpdate".  Now call the subroutine from above and pass it the ActiveSheet.  You're code on the Pivots Worksheet should look like this:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  'Call DateFilter to update dates filter to match data
  DateFilter ActiveSheet
End Sub

Save and close the VBA editor.  Now change the slicer selections again.  If the code is working properly, you will see that the date-axis is dynamic now and adjusts to the available data:

You can further customize this code if you wanted to find the maximum end date as well and filter "between dates..." instead if you like.

Conclusion

Hopefully this example shows you how you can create really powerful, dynamic PivotCharts for use in your own work.  By taking the time to configure these sorts of charts, you can quickly gain insights into your data and produce report-ready figures all on one chart and just change the slicers/filters and print.  As always, leave questions or comments below and I'll try to help!