Year on Year, Month on Month and Actuals for Current Month

Tableau makes it really easy to compare a metric to what it was in a previous period, such as a year ago (often referred to as 'Year on Year') or a month ago ('Month on Month'). These can be displayed using the table calculations. However, things become a little more tricky if we start combining this with filters.

What if we want to display Year on Year (YoY) as well as Month on Month (MoM) and current figure but we only want to display one month on the view.

First thing you would try to do is filter for current month but then the MoM and YoY figure go missing:

Since Tableau applies filters on the data source, once you filter say for the latest month only the comparison to previous year or previous month is not possible any more.

A little trick to get around this is to use a table calculation in the filter. Table calculations happen on the view rather than in the data source. This means that Tableau will bring in all the data, calculate MoM, YoY and then apply the filter.

So what calculation can we use for the filter? For example, you could set up a calculation to get the figure from next month. If we only have numbers until December 2014, then the January 2015 figure will be empty. This means that 'next month figure' for December 2014 will be empty. Add the new table calculation 'next moth' to filter and select 'special' -> 'null values'.

You can download an example below.

Learn About Tableau