We tackle lots of curly questions here at Data Runs Deep, and here’s a particularly interesting one that came across my desk.
“Can we visualise (dynamically) in a table: last month, month before last, last month last year, last month two years ago e.g. Sept 2019, Aug 2019, Sept 2018, Sept 2017?”
If you’re familiar with the ins and outs of Data Studio, this is an exciting one as tables are fixed displaying the data in ascending or descending order. There are options to view comparisons with a previous period, but no more than one period.
But now we can! Let’s get to creating your very own dynamic table. Here’s how it’s going to look:
First off, let’s get you familiarised with a dimension called Month Index. In a specified date range, the Month Index is 0 for the first month, 1 for the second month, and so on. Going back to the question of visualising Sept 2019, Aug 2019, Sept 2018, Sept 2017 (I know I know, I’ve gone a step extra for 2016 in my example) we lay out the maths for our index.
There are 12 months in a year and the Month Index for the first month is 0; so we are grabbing 0000 for Sept 2016, and 0012 for Sept 2017.
Final list will be as follows:
- 0000 - Sept 2016 (or first month of your date range
- 0012 - Sept 2017 (add 12 to grab the following year)
- 0024 - Sept 2018
- 0035 - Aug 2019 (subtract 1 to capture the previous month)
- 0036 - Sept 2019
If you’re after a two year comparison instead of three, subtract 1 from the Month Index at the 3rd year. For example:
- 0000 - Sept 2017
- 0012 - Sept 2018
- 0023 - Aug 2019
- 0024 - Sept 2019
Always cross check the Month Index with Month of Year to ensure you’re grabbing the right figures! You can perform the check like this:
Once you have identified the Month Index figures to be used, create a filter and apply it to the table like this:
Remove the Month Index from your table and it’s ready to go! As the name implies, it works dynamically within the chosen date range and with other filters.
Go forth and impress with your dynamic tables!