Answering my own question. I knew it was a brain fart:
Using a table calc:
ZN([Score]) - LOOKUP(ZN([Score]), -1)
and Compute Using Date.
However, I'd be interested to see if there's another calculation without the need for a table calc. I'll leave this open
2 of 2 people found this helpful
Since this is the formula of the difference from prior quick table calculation that's the fastest way to build it out (just a few clicks) and will have the fastest performance (since it's a computation in Tableau on the marks in the view).
The other options are:
1) use a Measure Names/Values crosstab when there's a measure for each year. This could be built out via regular aggregates or Level of Detail expressions (or both), but would require creating a new measure each new year.
2) use a measure built using Level of Detail expressions that is essentially a complicated SUMIF.
3) use a Tableau data blend where the linking dimensions are the Business and Year, only the year is offset in the secondary source so via the blend you get the prior year and current year.
4) use Custom SQL (if available) do a self-join on Business and Year, where the year is offset. In Tableau v10.2 (in beta) we will be able to do this without Custom SQL using the new Join on Calculations feature.
The last two years at the Tableau Conference there have been sessions on "which kind of calculation should I use" and the starting place is if the data exists in the view then a table calculation is typically the place to start, if the data doesn't exist in the view (i.e. you want a calculation at a finer grain). You can find the TC16 sessions at http://tclive.tableau.com, I don't have the names offhand.
Would you be willing to walk us through Options 1 and 2?
I want to respect your time, but that would be incredibly helpful.
Also +1,000 for that link. Didn't know that was an option. There are tons of videos, but I'll look for this topic.