On the right, I'd like to sort the customers (or sub-categories) by the most recent difference in Sales (december vs. november) in order to quickly see why sales were higher or lower in december. The color in these tables already indicate the difference. However, I can't seem to get the table calculation to

be limited to only the most recent period, and

make it appear in the sort dialog.

I could only imagine it working with an LoD calculation combined with a Table calculation, which AFAIK isn't possible(?). What other options do I have of accomplishing this sort?

So first we need a formula, which contains the var on the last month, for every row (Customer ID) regardless of the month (i.e. even if we hover over a march cell, this still contains the value of the last months var, to previous month)

We can do this with

[Month over month diff sales - Last Month SORTER]

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

However, we won't be able to use this as our sort (as it's a table calc)...but Tableau sorts blue (discrete) pills in ascending order by default....so we can make this discrete and then bring it in before [customer],to force the sort...so as this, by default, sorts in ascending order, and we want descending order...we can just reverse the sign on our formula

[Month over month diff sales - Last Month SORTER]

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

Then we can use this to force the sort. In the attached I've left this field un-hidden so you can see what it does, but in the final version you can hide this header!

Thanks Micheal, glad you found it useful...I do a bit of Tableau training here and there, and spend (for a starter group) 1/3 of the first day (2 day course) on just the concept of blue and green pills. Once you understand them you can generally create almost anything in Tableau, with enough imagination of how to use these elementary principles.

The below link, although written back in 2011 (so a fairly primitive Tableau, by today's standard!)...is the one I still reference the most (it is the core of Tableau, IMHO...it was the genius idea that made Tableau 1.0 so an awesome bit of software, and is still the basic principle about how you think about turning a viz in your head, to rendered in Tableau...even in Tableau 10.0!!)

So if we use the ZN(SUM([Sales])) and/or LOOKUP(ZN(SUM([Sales])),-1) then the values these 2 formulas take change month by month...which is what the colouring ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1) formula we have. If we use this, and as a discrete field, each Month takes a different value, and so we get multiple entries per customer...If I use this version of the formula (and restrict the view down to one customer) you'll see what I mean

There is a value for Last() is 1,666....so Last - November (LOOKUP(-1)) is 0-1,666 = -1,666 and for October Last() is still 1,666 and LOOKUP(-1) is 1,794 (so 1,794-1,666=128)...and as we're using them as discrete both values will create a row (for that customer).

By hard-coding Last() and Last()-1 every value for a customer (regardless of the month) is the same calculation, so we just get the one value for every row for Amy Cox

(btw in this one customer example I had to add the 'Show Missing Values' else it will just take Dec from Oct....in the OP, as all customers are in view we have all months populated so the Last()-1 always looks at November).

not sure I've explained that the best way!, but hope that sheds some light on why we've done it this way? Let me know if not, and I'll attempt to explain it differently/more-clearly!!

I've approached this thinking we want exactly the same vizLoD as our example...and have used LoDs. It's a bit complicated, but the 'how it works' tab should shed some light

I've also restricted the data to only show up to October 2014 (else data goes to December and that's the one month this is easy!!). I've added this as a context filter, so our LoDs think that this is the last date in the data)

So first I want to dynamically find the last month of data...so I use this formula

[Max Month of Data]

DATEPART('month', {MAX([Order Date])})

btw {} with no FIXED is just the short-hand for a FIXED LoD where I have no dimension to run it over...i.e. the whole data set)

I then use this, and a (similar) [Max Order Date Year - LoD] {MAX([Order Date Year])} to create my 2 YtD values (one for TY and one for LY)

I then need to use these to create my YoY...however as you have Month in the VizLoD I need someway of getting a single value applied to all Months (I've opted for another FIXED LoD!) but we could do this with INCLUDE/EXCLUDE LoD, or Table Calcs)

So I create my YtD var as

[Sales YtD var - by Customer]

{FIXED [Customer Name]:SUM([Sales YtD - TY])}

/

{FIXED [Customer Name]:SUM([Sales YtD - LY])}-1

fixing it to customer name

and then we reverse the sign (and make it discrete) to create our sorter!

[Sales YtD var - by Customer sorter]

ZN([Sales YtD var - by Customer])*-1

Hopefully this makes sense (I've added some of these calcs to the tooltips to help the understanding), but please post back if not.

btw I've split these formulas out to help the understanding, but could be nested into one or two in your final solution