KPIs: Current Month To-Date Profit versus Same Month Last Year To-Date Profit

Current Month-to-Date vs. Same Month Last Year (SM-LY) KPI

Key Performance Indicators (KPIs) are powerful additions to any data visualisation, and adding date context can provide helpful context. In this blog post, I will walk through how to create a Current Month-to-Date (CMTD) versus Same Month Last Year (SM-LY) KPI in Tableau.

For this example, I have chosen to use a Parameter to dynamically select a "Current Day.", allowing for historical analysis or testing. Alternatively, you can simply use the TODAY() function in place of the parameter in all calculations for a literal, real-time KPI. Choose the method that best fits your analysis requirements!

Step 1: Set Up the Date Selector Parameter

First, to set up the Parameter, click the top right drop-down arrow on the Data pane and select Create a Parameter... Set the data type to Date. I have named mine Date Selector.

Once created, right-click the new parameter at the bottom of the Data pane and select Show Parameter to display the selector on your visualisation.

Step 2: Create the Current Month-to-Date (CMTD) Calculation

Next, let's create the Current Month-to-Date (CMTD) calculation. Click the drop-down arrow in the top-right corner of the Data pane again, this time selecting Create Calculated Field... Here is the formula:

Let's break down this calculation.

1. The first condition checks if the month of the Order Date matches the month of the Date Selector parameter.
2. The AND condition then ensures the Order Date is less than or equal to the selected date.

If both conditions are met, the calculation returns the Profit (or your chosen measure). Essentially, this captures the cumulative profit for all days in the current month up to and including the selected "Current Day." These date-context KPIs can be tricky initially to wrap your head around, but they become much clearer with practice.

Step 3: Create the Same Month Last Year-to-Date (SM-LY) Calculation

Next, let's calculate the Same Month Last Year-to-Date (SM-LY) profit. Create another calculated field, this time using the following formula:

This calculation is similar to the previous one, but with key modifications to shift the date context back one year.

In the first part of the statement, we use the DATEADD('year', -1, ...) function to shift the month of the Date Selector back exactly one year. This ensures we are comparing the profit from the same month a year ago.

The second part of the statement (the AND condition) ensures that the Order Date is less than or equal to the same day of the month, but one year prior. By applying the date logic to the Order Date and the -1 year date logic to the Date Selector, we isolate the relevant profit.

Step 4: Display the KPI

You can now drag both of these calculations onto the Label option on the Marks card. Then, click the Text button and the ... ellipsis to edit the formatting and style your KPI labels to make them "pop":

And there you have your powerful, contextual KPI! By calculating the Current Month-to-Date profit alongside the Same Month Last Year profit, you provide critical comparative data, empowering users to make better-informed decisions.