In a previous post I walked through a LOD (level of detail) based alternative to the LOOKUP table calculation. In that example I was looking at sales last month, and in a recent Tableau Forums question someone was asking if it could be extended to determine average sales from the previous three months.

The answer was “yes” and you can check the whole thread on the Forums, including other options. The main difference from my previous post is that we needed to extend the group numbers from two to four, so that any given month falls into 4 “higher level groups” that we can target with a LOD. We then chose which LOD to use based on the index (_i).

The LOOKUP table calculation in Tableau is really handy when you want to show or use a value from a previous row in the view. For example if you are showing sales per month and need to use the sales figure from the previous month to calculate month-on-month growth.

In the example above our calculation for “Sales last period” is:

LOOKUP(SUM([Sales],-1))

LOOKUP is great when the level of detail you want to look back through is in the view (e.g. order month is in the view if you want to lookup sales for the previous order month). But things get tricky when that isn’t the case. And people often hit constraints when they want to use the result of a table calculation like LOOKUP in another calculation / aggregation. It’s not unusual at this point for people to ask if they can use a level of detail / LOD calculation for looking up the value from a previous month, year, or category. And unfortunately the answer is almost always “you can’t use a LOD for that”, but can you? Spoiler alert: yes!

The trouble with LODs

LOD calculations allow you to break out of the level of detail that your calculation is working on. So if your calculation is working at the month level you can break out to the quarter level, to ask questions like how does this month compare to the quarter. In the example below we could use a LOD to calculate how much the sales for 2022-06 differ from the average for the quarter that that month is in;  breaking out to a higher level grouping.

But if you’re after previous month there is no single dimension or higher level grouping that will tell you that; each month has a different previous month (unlike the case above where three months share the same common quarter). The previous month for 2022-06 is 2022-05, but 2022-06 is itself the previous month of 2022-07. Any one month is in two groups when it comes to determining previous months.

Possibly as you read that last sentence an idea will be forming! Let’s pursue that idea that there are two groups in play…

A LOD for LOOKUP(…,-1)

If we put each month into two groups which are offset from each other by a month then we can calculate two previous values, one of which will be right for odd numbered months and the other for even numbered months in a sequence of months. Let’s take a look at this in action:

Firstly we need an continguous sequence number for each row (in our case each month). With months that is easy as we can use a DATEDIFF to calculate the number of months since the first month in the data set.

_i = 

DATEDIFF(
 'month',
 {MIN(DATETRUNC('month',[Order Date]))},
 DATETRUNC('month',[Order Date])
 )

Using that sequence number we can create the two offset groupings using integer division.

_iGroupA =

DIV([_i]-1,2)

_iGroupB = 

DIV([_i],2)

This gives us a grouping for each combination of two months:

February will be in Group B when we want it’s previous month, and in Group A when it is the previous month.

As we now have a higher level grouping that we can target in a LOD, we create two LOD calculations to get the other value in the group…

_iPrevA =

SUM({FIXED [_iGroupA]: SUM([Sales])})
- 
SUM([Sales])

_iPrevB = 

SUM({FIXED [_iGroupB]: SUM([Sales])})
- 
SUM([Sales])

These calculations get the sum of sales for each grouping of two months, and then subtract the sales for the month we’re in. Hence we end up with the sales for the other month in the grouping. Now we just need to work out which of these LODs to use for each month. To do that we switch between them…

iSwitch =

([_i]-1) % 2

The modulus operator here gives us the remainder of dividing by 2 and hence a number that flicks back and forth between 0 and 1. So…

Sales last month (LOD) =

IF MIN([_iSwitch])=1
THEN [_iPrevA]
ELSE [_iPrevB]
END

You can check out the workbook for this on my Tableau Public profile: LOD equivalent of LOOKUP.

I’ve used this approach for a number of gnarly Tableau Forum questions and I think I’ve seen similar crop up once or twice before but it’s not a particularly well known approach. Often people will fall back on solutions like self joins – and to be fair shaping your data to avoid having to jump through hoops like the above is not a bad idea. But if you do need it, I hope that you find it useful!