3 ways to calculate the difference between two months in Tableau

Suppose you must compare two dates to show how sales changed (i.e., quarterly, semi-annual, or year-to-year). In that case, you might be interested how to calculate this delta. You can analyze any business metrics, such as market share, turnover, conversion rate, and the number of subscriptions. While preparing a management dashboard, I found three possible ways to calculate and visually represent the difference.

Case study

For example, in this article, we will calculate the difference between December and July and present the results in numerical format with arrows showing both growth and decline. The dates should be delivered in columns. The delta between two months should contain an arrow positive/negative depending on the result. Your report will be based on sales. The columns will be the dates and rows — the sales data.

The picture below depicts what we want to achieve.

Monthly values and delta (difference between two months)

Here I would like to explain to you how it works. The analysis is based on a standard database Sample-Superstore. As you can see, 28,765 sales were made during July 2020 and 75,522 during December 2020. The delta is equal to 163%.

You can find Tableau Workbook with the calculations for download here.

Solution

1 Way — Quick Table Calculation, Difference

Here’s how you can calculate the difference in sales between July and December using a quick table calculation in Tableau:

• Create a new worksheet and drag the “Sales” field to the Rows shelf.
• Drag the “Order Date” field to the Columns shelf.
• Right-click the “Order Date” field in the Columns shelf and select Custom — “Month / Year”.

• Click and drag the “Order Date” field from the Columns shelf to the Filters shelf and filter December 2020 and July 2020.

• Click and drag the “Sales” field from the Rows shelf to the “Label” card.
• Right-click the “Sales” label and select “Add Table Calculation”.
• In the “Table Calculation” dialog box, select “Difference” or “Percent of difference” under “Calculation Type” and “July”.

• Choose “Order Date” under “Compute Using.” Click “OK” to apply the calculation.

So you will get the following results in the view.

The problem is that your delta is placed in the same cell of the December
column view.

Even if you want to specify the rows creating special cards, the delta will not be separated in a distinct field. Adding a value with the number 1 may not help.

Nevertheless, you are lucky because you can solve this problem manually. Now let’s look at two other options.

2 Way — Custom Calculation DATEPART

Obviously, the logic lies in the following steps:

1. Identify a value for the particular months
2. Subtract the difference
3. Assign an arrow to visually represent the delta

I recommend performing all basic calculations, e.g., value identification in Tableau Prep, to make them cleaner and more precise.

Since we need to extract specific parts of a date in a particular month/year, we would like to use the DATEPART function. That is the second date type available in Tableau when you apply any date dimensions on your dashboards.

1. Identify a value for the particular months

You can follow these steps:

• Create a calculated field to represent the monthly sales in December. You can name this field “Value Sales Dec 2020” and use a formula like this:

SUM(IF DATEPART('month', [Order Date]) = MONTH(#2020–12–03#) 
AND DATEPART('year', [Order Date]) = YEAR(#2020–12–03#) THEN [Sales] END)

Please don’t mess up the MONTH formula. Although we enter the date in the format yy-mm-dd, Tableau will return only the month of a given date as an integer. In this case, it will be “12” — December.

In general, we want to sum up the month of December and the year 2020 and show us the number of Sales for the given period.

• Create another calculated field to represent the monthly sales in July (follow the same logic). You can name this field “Value Sales July 2020” and use a formula like this:

SUM(IF DATEPART('month', [Order Date]) = MONTH(#2020-07-01#) 
AND DATEPART('year', [Order Date]) = YEAR(#2020-07-01#) 
THEN [Sales] END)

In this case, July will be the “Previous Month sales” while December is the “Current Month sales”.

2. Subtract the difference

• Create a third calculated field to represent the difference between the two months. You can name this field “Month-to-Month Difference” and use the following formula:

([(2 way) Value Sales Dec 2020]-[(2 way) Value Sales Jul 2020])
/[(2 way) Value Sales Jul 2020]

You can now see the difference in sales between the two months.

3. Assign an arrow to visually represent the delta

The formula for the arrows is below.

And here is our result, where the delta is placed in a separate field.

3 Way — Custom Calculation DATEADD

Finally, I would like to show you the third option.

1. Identify a value for the particular months

You can follow these steps:

• Create a calculated field to represent the monthly sales in December. You can name this field “Value Sales Dec 2020” and use a formula like this:

SUM(IF MONTH([Order Date]) = MONTH(DATEADD('month',-3,TODAY())) 
AND YEAR([Order Date]) = YEAR(DATEADD('year',-3,TODAY())) THEN [Sales] END)

Here we are using another date function DATEADD that allows us to add a specified time interval to a given date or date-time value. In this case, “month” will mean the time interval you want to add, “interval” is the number of datepart units you wish to add, and “TODAY” is the starting date or date-time value to which you want to add the specified interval. So, if we want to retrieve the 2020 year in a database in 2023 (it’s currently 2023 year(, we should write “-3,TODAY” where -3 equals 2023–3=2020. The same goes for months. As I’m writing this article on March 15th, this means that to specify December, we should write: “-3,TODAY” where -3 is equal to March, February, January, and finally, December.

• Create another calculated field to represent the monthly sales in December. You can name this field “Value Sales July 2020” and use a formula like this:

SUM(IF MONTH([Order Date]) = MONTH(DATEADD('month',-8,TODAY())) 
AND YEAR([Order Date]) = YEAR(DATEADD('year',-3,TODAY())) THEN [Sales] END)

2. Subtract the difference

([(3 way) Value Sales Dec 2020]-[(3 way) Value Sales Jul 2020])
/[(3 way) Value Sales Jul 2020]

3. Assign an arrow to visually represent the delta

IF [(3 way) Month to Month difference] > 0 THEN "⬆️"
ELSEIF [(3 way) Month to Month difference] < 0 THEN "⬇️"
ELSE ""
END

After applying the formula, your results will look like this

Regarding the arrows, there are two possible methods to implement that:

1. Through a calculated field
In this case, the arrows will be applied to all your KPIs.

2. Through the number formatting

Check here if you need more information about the arrows.

Your results will look like the below.

You can apply this method for multiple financial dimensions and build a beautiful customer/management report.

Have a nice day.