Avoid the Current Month Drop-Off

The following blog post was written as a collaboration between Kevin Flerlage and Ethan Hahn, both of Unifund, CCR. If you don’t know Ethan, he is a gifted in SQL and equally gifted in Tableau.  He’s also HILARIOUS!!!  You should absolutely follow him on Twitter.

The Problem

It’s normal for dataviz practitioners to visualize measures over time, but how do you handle the current month?  If it’s at the beginning or even the middle of the month, it looks like the entire company needs to be fired. 

For example, check out this chart using some sample data that I created.  In this case, we are going to assume it is Dec 3.  Our sales look pretty good and then, oh crap!  What the heck happened?  Why that huge drop?  Did everyone stop working completely?  Well, the truth is, it’s Dec 3 so we are only capturing 3 days of sales.  There is probably nothing wrong at all, but that’s not the reaction that this chart elicits.  In fact, it immediately elicits fear, anger, and worry.

So how do we address this?  Well, we can continue to show it and cause a few extra heart attacks among senior leadership or we can exclude it and hide crucial information from our stakeholders.  Well, as you probably guessed, there are much better way of addressing this issue. 

This blog post and associated visualization will break down eight different ways of visualizing the current month while avoiding the “current month drop off”. 

The History

I was building a dashboard and brought it to our bi-monthly dataviz meeting.  That meeting includes Dinushki De Livera, Ethan Hahn, Jeff Shaffer, and myself.  I presented a dashboard that had within it, the heart attack maker – the current month drop off.  As Ethan Hahn says, “Our business frequently charts revenue on a monthly basis, but a lot of our money comes in right at the end of the month.  That means unless you’re looking at a chart after close of business on the last business day, it looks like your performance has totally gone off the rails. There’s nothing visually distinguishing my partial month from all the complete months, so it looks like we’re doing terribly when we’re not.”  So, I brought it up as a discussion point to review our options.  Do I just remove it?  Do I annotate it? 

Now my favorite line from this blog post, which of course comes from Ethan: “This is where working with Jeff comes in handy”.  Jeff simply suggested that we “break the line”.  Break the line?  Yep, break the line. 

The “Break” trick is shown in the associated viz as the second chart under the header of Break.  As Jeff states, it simply breaks the line and separates that current, partial month from the rest of the full months.  So how do we do it?  The remainder of this blog post will show you how to “break the line” as well as provide several other methods for representing the current, partial month in your visualizations.  All of this started with the break the line method, but then I had a ton of fun just creating variations on that method and creating a few new ones.  (Note: we will focus solely on visualization by month, but we could do the same thing by day, week, year, etc.). 

Break

Jeff’s method was, as Ethan put it, “brilliant and simple”.  But before we move on, let’s build the original chart shown above.  First, place Date on Columns and set it to Continuous Month & Year.  Then place Sum of Sales on Columns.  Create a dual axis and set the secondary axis mark to circles (be sure to synchronize your axes).  Place Date on filter and show 1/1/2019 – 12/3/2019.  Finally, add that filter to context because spoiler alert, we will be using Fixed LODs.  This exact chart is shown under Original in the associated viz.   

Now, how do we break the line?  Well, it’s quite simple actually.  We leverage a fixed LOD (for this blog post, I have preceded all calculations with a @ symbol) to return TRUE for any Order Date that matches the most recent month and FALSE for all the other Order Dates.  First, create a calculation called @Max Month:

{ FIXED : MAX(DATETRUNC('month', [Date]))}

This calculation will determine the last month in your data.  Next, create a calculation called @Break for Max Month:

DATETRUNC('month', [Date]) = [@Max Month]

We then drag this pill onto the Color card of both axes (All).  This discrete pill will then “break the line”.  The FALSE values will be one color and the TRUE values (not the hardware store, just the max month in our view) will be another color.  And that’s it!  To see exactly what this looks like, check out the “Break” chart in the associated viz.  (Note: I also added this pill to the size card on the circle axis so that I could make the circle for the current month a bit larger than the previous, full months). 

As Ethan said, “You’ve separated the partial month, so you can see the true trend over time; you’ve still got December’s data, so you know where the current month stands; and you didn’t even have to use your second axis to do it!”

Important Side Note

I will note, however, that this is not our preferred method at the office.  The above technique finds the max month within your data and considers it an incomplete month.  This may be exactly what you want, but what if today is Apr 2 and the last record in your data is from Mar 31.  Using the technique described above, you would consider March the incomplete month when in reality, the month is already over.  In most situations, it is better to not look at the max month in your data, but to look at the month of today’s date.  An example of this would be accepting payments from customers.  If you receive a payment on April 2, will it be applied to April 2 or could it be backdated to March 31?  In a case where payments might be backdated, then perhaps using the above technique would be best.  But if payments will be applied on the current day, then using Today is probably best.  With dense data or when it is closer to the middle of a month, the two may yield the same results, but please be careful in choosing the right option. 

Okay, all this talk about the month of today being the preferred method in most cases…how do we do it?  Well, all you really need to do is replace the second part of your @Break for Max Month calculation.  The original calculations is as follows; I’ve highlighted the second portion.

DATETRUNC('month', [Date]) = [@Max Month]

We need to replace the [@Max Month] with DATETRUNC('month', TODAY()):

DATETRUNC('month', [Date]) = DATETRUNC('month', TODAY())

This will essentially set the max month to be the month of the current day rather than the month of the last record in our data.  Unfortunately, it is difficult to represent this in my Tableau Public viz because the source data is sample data that will not be updated (like it would at work).  It would work for the month in which I publish it, but would stop working after that month has ended.  So, this blog post will utilize the Fixed LOD method, but you can swap them out. 

Seven Variations

As I mentioned previously, all of this got me to thinking and on the evening after that meeting, I created seven more variations and techniques to avoid the current month drop off, many of which utilize Jeff’s technique.  The remainder of this blog post will walk through those examples.  For each section, please reference each chart shown in the associated visualization.

Reference Band

The next variation does not break the line, it simply utilizes a reference band that runs from the most recent full month (in our case, November) through the beginning of the next month (in our example, January).  To do this, we need to determine what those months are and ensure it will update dynamically over time.  We will create two calculations, one to determine the previous month and one to determine the next month: 

          @Max Month – 1

                { FIXED : MAX(DATEADD('month', -1, DATETRUNC('month', [Date])))}   

          @Max Month +1

                { FIXED : MAX(DATEADD('month', 1, DATETRUNC('month', [Date])))}

Now Place these two pills on the detail for All axes.  Next, right-click on the X axis and choose to Add Reference Line.  In the window, change it to a reference band with Band From set to @Max Month – 1 / Minimum and Band to set to @Max Month +1 / Maximum (set colors and labels as you wish).  When you are finished, you will have a reference band surrounding your current, partial month.  See the Reference Band chart in the associated viz. 

Break with Reference Band

Break with Reference Band is simply the combination of the two previous charts.  Place @Break for Max Month on Color for all axes and on size for the Circle axis.  Next, place @Max Month – 1 and @Max Month +1 on detail.  Finally, create a reference band like above.

See “Break with Reference Band”. 

Reference Line

The Reference Line method simply shows a reference line, which measures sales for the current, partial month.  To do this, we need to calculate the sales for the current, partial month.  Remember, over time this will change so it must be dynamic.  Create a calculation to pull sales for the current month and call it @Sales for Max Month Fixed:

          { FIXED :

                   SUM(

                              IF DATETRUNC('month', [Date]) = [@Max Month] THEN [Sales]

                             END

                   )

}

Add that to the detail card.  Right-click on your Y axis, add a reference line, and choose this calculation.  I chose to use a dotted line with a label of Partial Month $xxx.xx.

See “Reference Line”. 

Break with Reference Line

This technique is a combination of Break and the previously discussed Reference Line.  Put @Break for Max Month and @Sales for Max Month Fixed on detail.  Then add the reference line as shown previously. 

See “Break with Reference Line”. 

Break with Area Chart

Break with Area Chart is the exact same technique employed in Break section except that the line chart has been changed to an area chart.  In this particular example, @Break for Max Month is placed on detail, but not on color or size, although you could put it on size, color or both.

See “Break with Area Chart”.

Forecast with Break

When I worked out the details for this one, I was pretty excited.  This method provides you with both the current sales for the partial month as well as a forecast based on previous months.  To do this, start by creating a calculation called @Sales for Max Month:

         

          IF DATETRUNC('month', [Date]) = [@Max Month] THEN [Sales]

END

This calculation will return Sales for the max data and null for any other date.  If you are starting with the Original chart, you have MONTH(Date) on Columns and a dual axis with two SUM(Sales) pills.  For this chart, replace the second SUM(Sales) with @Sales for Max Month.  Next, right-click inside the chart and click on Forecast → Show Forecast.  It should now look like this:

Next, go to the Line axis and click on the down arrow (right side) of the Forecast Indicator pill and change it to Attribute.  This will connect the forecast as shown below:

Clean up the chart by hiding one axis and hiding the No Forecast indicator at the bottom right (the “no forecast” warning is on the axis with just the circle). 

Now this is pretty slick.  You have both the current value and the expected value in a single chart.  This provides a ton of information to you users.  Please keep in mind that the forecast is based on the previous months and not the current month.

See “Forecast with Break”.

Forecast with Break and Reference Band

And finally, my absolute favorite option of all.  This technique shows the current, partial sales value, the forecast based on previous data, as well as a reference band like we used in Reference Band section above.  That said, it’s a bit tricky as we cannot just add pills to detail, we must create some dynamic parameters.  If you aren’t on version 2020.1, this may be difficult to do (please let me know if you come up with a way). 

Let’s start off with the chart we created in the Forecast with Break secion.  Next, we will create two parameters.  The first will be called @Ref Band Min.  Make it a Data Type of Date and allow all values.  Now, we will use Dynamic parameters to bring in the previous month that we used in the Reference Band technique previously.  Click the drop-down next to “Value when workbook opens” and choose @Max Month -1.  Click OK.  When the workbook opens, it will automatically populate this parameter with the month prior to the current, partial month. 

We will now do the same thing with the next month.  Create a parameter called @Ref Band Max.  Make it a Data Type of Date and allow all values.  Now in the dropdown next to “Value when workbook opens”, choose @Max Month +1.  Click OK.  When the workbook opens, it will automatically populate this parameter with the month following the current, partial month. 

To add the reference band, right-click on the X axis and choose Add Reference Line.  At the top, change it to a Band.  In the Band From dropdown, choose the @Ref Band Min parameter.  In the Band To dropdown, choose the @Ref Band Max parameter.  Now simply format the reference band as you like. 

The result is the best of all worlds with the current partial month value, a reference band, and a forecast. 

See “Forecast with Break and Reference Band”.

Wrap-Up

As you can see, there’s tons of options to show the current, partial month in a way that avoids cardiac arrest amongst your leadership team.  Since the development of this technique, we use it constantly at our office and we hope you will too. 

A special thanks goes out to Jeff Shaffer & Dinushi De Livera as well as Ethan Hahn who co-wrote this blog post with me.  Thank you!

Kevin Flerlage, April 27, 2020

Twitter| LinkedIn | Tableau Public