“Lost time is never found again.” – Benjamin Franklin.

So said the man on the US $100 bill. Fortunately, a lot has changed since the 18th century, including the ability to find lost time in time series data. In the first of my new quick tip series, we’ll look at how to do this using the first Makeover Monday dataset of 2020!

There are two possible approaches to this challenge – both involve converting Year into a Date data type. Right-Click + Dragging Year onto columns will give the option of using it as a:

Because measures are continuous, selecting the green option for Year(Year) will let you skip to the last step in this post (Bring the dots back). If interested in how to do this with Year(Year) as a dimension, read on!


Lost Time

If you’ve selected the dimension, you’ll notice the data includes years 2004-8, 2013 and 2017 (skipping over both the ’09 swine flu pandemic and ’14 Ebola epidemic – of course nothing to do with the dataset). The equal spacing makes this misleading.

  1. Ensure the dimension is in the Date or Date & Time format.
  2. Right-click the axis and select Show Hidden Values.

Quick Tip Time Series Animation Part 1


Join the dots

The next part is a little more hidden, but straight forward once you know.

  1. Right-click in the chart to bring up the formatting options.
  2. Select the measure as the field.
  3. Change to the Pane tab.
  4. At the bottom under Special Values change Marks to Hide (Connect Lines).

Quick Tip Time Series Animation Part 2


Bring the dots back

Finally, our original intent was to make the chart look less misleading and more visually appealing. Note that it appears as though we have data for all years 2004-17, which is not the case.

The final step is to bring back the dots so that we know which years have data and which don’t.

  1. Click colours, then click the line with dots next to Markers.
  2. Volla!

Quick Tip Time Series Animation Part 3




As always, thanks for taking the time to read my blog! If you have any comments, suggestions or want to chat, free to connect with me on my LinkedIn!
~ Ryan Edwards


Ryan Edwards
Author: Ryan Edwards

BCom BSc from the University of Auckland. Prime Minister’s Scholar & Ex. Toastmasters President.