The reason for the behavior you are seeing is that the grand total lines are calculated separate from everything else. When you use a table calculation such as LOOKUP, you will often see that nothing appears in the total row because it is a single row. There are no rows before it or after it -- so nothing to lookup.at any offset, except 0.
Now, I love table calculations. They can solve almost any problem and we could even make it work in this case (see Jonathan Drummey's excellent post here to see see how to customize grand totals: Customizing Grand Totals – Part 1 | Drawing with Numbers - be sure to check out the subsequent parts too).
But in this case, I might suggest using data blending on a copy of the data source as an alternative that is simpler and maybe slightly more intuitive. The key is that we'll create a calculated field in the primary source that gives us the value for last year at a row level:
We can then use that to blend to a copy of the data source making sure to set the relationship so we blend from Last Year (in the primary) to Year (in the secondary).
That gets us this year's and last year's values together.
I've attached your workbook so you can see it. Please let me know if you have any questions!
Lookup last year -JNM.twbx 46.4 KB
Thank you for your help.
This would indeed solve this exact problem, and still make it manageable. Except the actual report needs 4 columns that find some value from another period. year-1 year-2 month-1 period-1. So that would make 5 connections. There are 30 to 35 datasources. So I need about 150 connections instead of 30.
I actually hoped that I could create a field that finds a value based on a query in the same datasource. (like in excel vlookup). And that this column could be summed.