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.

Base Data - Bar Chart

(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]) }

First Calculation - 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]

Second Calc - Boolean added

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]) )

Third Calc - last year 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]) )

4th calc - absolute difference

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]) }

5th calc - lowest absolute 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]

6th calc - boolean

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

7th & 8th calc - sales

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.

 

BANs

 

So sometimes when doing date calculations, close is good enough

Author
Daniel Caroli

Daniel Caroli

Senior Analytics Consultant & UK Team Lead at Biztory.

Read more articles of this author
Let's discuss your data challenges

Join our community of data enthusiasts

Get industry insights, expert tips and Biztory news sent straight to your inbox with our monthly newsletter.