It's pretty common to see KPIs in a business dashboard showing a current year measure vs the same measure from the previous year. These KPIs can come in lots of different forms and in this blog, I thought it would be great to walk through a few.
We'll work through some examples using a superstore dataset.
Current Year Profit vs Previous Year Profit
Let's start simple.
To calculate the current year profit, you first have to think about how to isolate the current year. The logic behind this will simply be if the year of the date field (In the superstore dataset, this will be order date) matches the year we are looking at, then show the profit. If the data is up to date, we can use the TODAY() function to calculate the current year. (If not, you may have to use a parameter to select the date you are after or find the max date in your dataset (however, this will require a FIXED LOD).
We can use an IF statement to do this.
First, in order to capture the year from our date field and current date, we can use the DATETRUNC() function. DATETRUNC() will truncate your date to the first date of whatever date part you specify. So if you do DATETRUNC('year', 18/03/2024), the formula will output "01/01/2024".
So...
IF
DATETRUNC('year', [Order date]) = DATETRUNC('year', TODAY())
THEN [profit]
END
As all of the dates will simply be truncated to the 1st day of the year, if the years are equal, the function will output the profit values for that year. If they're not equal, nothing will be output.
You can also use :
DATEPART('year', [Order Date]) = DATEPART('year', TODAY()) or simply:
YEAR([Order Date]) = YEAR(TODAY())because it directly extracts the year number rather than relying on truncated dates.
We can check this by building out a table.
• Drag [Order Date] onto rows as MDY (month, date, year)
• Drag your calculation into the view
• If your sales values only show for the current year, it's working
Now that we've calculated the current year profit, we need to calculate the previous year's profit. To do this, we can use the DATEADD() function.
DATEADD() allows you to add to a date with a chosen interval. As we want the previous year, we must take our current year calculation, and using the DATEADD() function, - 1 year from our current date field.
IF
DATETRUNC('year', [Order date]) = DATEADD('year', -1, DATETRUNC('year', TODAY()) )
THEN [profit]
END
Checking our table shows us that this calculation works!
Once we bring those into the view by themselves, they will be aggregated to show you the full sum of profit for the current year vs the previous year.
Current YTD vs Previous YTD
What if you wanted a bit more flexibility? Instead of always using today’s date to calculate the current year’s profit, you could allow the user to select a custom date. We'll call this simple parameter [Date Select] With this, you could return profit for the year of that chosen date, but only up to the point in time they’ve selected.
We can use similar logic as the previous section but with an extra little tweak. Thinking about what we want, we want to only output the profit values for the dates between the start of the year and the date we have selected. We can still use an IF statement but we can now use the < and > operators to essentially make a date range.
IF
[Order Date] >= DATETRUNC('year', [Date Select])
AND
[Order Date] <= [Date Select] THEN [Profit]
END
This is essentially saying, if the Order Date is between the start of the selected year and the date that has been selected, output the Profit. We can check this with our table view.
Notice how we don't have to use DATETRUNC() for the [Order Date] or the second [Date Select]. This is because we want those to be at the day level, which is the default for Tableau.
Current Tax Year vs Previous Tax Year
Now this problem requires a bit more nuance as there is an extra step of thinking that is quite easy to miss on your first attempt (I completely missed it on my first attempt).
The tax year runs from 6th April to the 5th of April of the next year. We'll use our [Date Select] Parameter again, and build a calculation to let Tableau determine what tax year it falls into
Here’s the logic broken down:
- If the selected date is on or after 6th April
- The current tax year starts on 6th April of that same year.
- The end of the tax year is 5th April of the next year.
- So any [Order Date] between those boundaries will be included.
- If the selected date is before 6th April
- That means we’re still technically in the previous tax year.
- In this case, the tax year start date shifts back to 6th April of the previous year, and the end date becomes 5th April of the selected year.
We can then combine this logic with our Current YTD vs Previous YTD logic.
The calculation below handles both scenarios:
IF
[Date Select] >= MAKEDATE(YEAR([Date Select]), 4, 6)
AND [Order Date] >= MAKEDATE(YEAR([Date Select]), 4, 6)
AND [Order Date] < DATEADD('year', 1, MAKEDATE(YEAR([Date Select]), 4, 5))
THEN [Profit]
ELSEIF
[Date Select] <= MAKEDATE(YEAR([Date Select]), 4, 6)
AND [Order Date] >= DATEADD('year', -1, MAKEDATE(YEAR([Date Select]), 4, 6))
AND [Order Date] < MAKEDATE(YEAR([Date Select]), 4, 5)
THEN [Profit]
END
The MAKEDATE() function simply builds a full date from the year, month, and day you specify. For example, MAKEDATE(2024, 4, 6) returns 6th April 2024. In our calculation, we use it to set fixed boundaries for the start and end of each tax year, so Tableau knows exactly which dates to include.
we’re using YEAR([Date Select]) inside MAKEDATE to keep the calculation dynamic. This way, no matter which date a user picks, Tableau always builds the correct tax year boundaries for that selection.
In plain English, this says:
- If the chosen date falls after 6th April, calculate profit for orders between that 6th April and the following 5th April.
- If the chosen date falls before 6th April, calculate profit for orders between 6th April of the previous year and 5th April of the current year.
This way, no matter which date a user selects, Tableau will always return the correct profit for the current tax year up to that point.
Conclusion
Once you understand these building blocks, you can adapt them to almost any timeframe, whether that’s current vs previous month, rolling 12 months, or even custom tax years, and turn them into powerful KPIs and comparisons across your dashboards.