So you want to compare sales data from the most recent period to the same period last year.
That sounds easy enough.
But what if:
-
The periods are custom
-
They have inconsistent names
-
They have inconsistent start dates
-
They have inconsistent duration
The actual problem we need to solve is:
What is the closest period start date to the most recent period start date, but one year prior.
This is quite a specific problem, but the calculations used to solve it can be applied to any sort of date comparison.
The dataset is straightforward, we’ve got three fields:
-
[Date] - the start date of the period
-
[Date Period] - the name of the period
-
[Sales] - the data we want to compare
Lets see what that looks like visualised.
(These sales look more inconsistent than the period names, it's almost like they're random 🤔)
First, let's work out what the most recent date (or Max Date) is using a simple level of detail calculation. This is almost always the first step in any sort of date comparison calculation. We'll number the calculations so we can keep track of their creation order, and recreate the visualisation each time so we can compare any changes. You can also copy these calculations directly into Tableau Desktop.
//0. Max Date
{ MAX([Date]) }
We can use the Max Date calculation to check against all the dates in the dataset. This is a boolean calculation and will return either True or False for this dataset.
//1. Latest Date
[0. Max Date] = [Date]
The Max Date can also be used to calculate the same date, but one year prior. This will be the approximate date which we can use to try and find the closest period from the prior year.
//2. Last Year Approx Date
DATE( DATEADD('year',-1,[0. Max Date]) )
Using this approximate date, we can calculate the absolute difference between this and the dates in the dataset. We want the result to be returned in number of days.
//3. Start Date Difference
ABS( DATEDIFF('day',[Date],[2. Last Year Approx Date]) )
Visualising this calculation shows why the absolute value is key to solving this problem. We can now find the date with the smallest difference. If we hadn't used ABS( ), the following calculation would just return the date furthest in the past.
//4. Overall Lowest Value
{ MIN([3. Start Date Difference]) }
Then we use this as a check against all the calculated date differences, where it returns True, we’ll have found the start date that is the closest to one year prior!
//5. Closest Prior Date
[4. Overall Lowest Value] = [3. Start Date Difference]
Calculation number 1 and number 5, are both booleans and return True or False. We can use them to return the sales figures for just those two date periods.
//6. Sales - Latest Period
IF [1. Latest Date] THEN [Sales] END
And
//7. Sales - Prior Period
IF [5. Closest Prior Date] THEN [Sales] END
Then taking these two calculations, we can calculate the difference between the two values.
//8. Sales - Difference
SUM([6. Sales - Latest Period]) - SUM([7. Sales - Prior Period])
Which we can then use to create an informative KPI for our dashboard.
So sometimes when doing date calculations, close is good enough