Try: LOOKUP(SUM([Cost]),FIRST()) - LOOKUP(ZN(SUM([Cost])), -1)
That'll 'lock' in the first row. If you want some other row, the second for instance it's:
LOOKUP(SUM([Sales]),FIRST()+1) - LOOKUP(ZN(SUM([Sales])), -1)
You can also put it in terms of the last row:
LOOKUP(SUM([Sales]),LAST()) - LOOKUP(ZN(SUM([Sales])), -1)
You actually might find this formula a bit more helpful:
LOOKUP(SUM([Sales]),FIRST()) - LOOKUP(ZN(SUM([Sales])), 0)
By not looking at the previous value in the second clause, the values aren't calculated for the line before. So instead of this:
You'll get this:
Which is showing the difference from the first to the current row. Personally I think you should also switch the clauses so it shows the amount of growth from the first year to the current year in positive number.
Thanks for your your suggestions but none of the formulas have returned the result I need. It might be that my data is structured differently to what you expect.
Please note: the original formula: ZN(SUM([Cost])) - LOOKUP(ZN(SUM([Cost])), -1)
works fine except for the first period which needs to be locked.
The reason it doesn't show anything on the first line, is because there is no previous value to subtract. If you want to fill in that box you can write something like this:
IF FIRST()=0 THEN ZN(SUM([Cost]))
ELSE ZN(SUM([Cost])) - LOOKUP(ZN(SUM([Cost])), -1)
But you're going to confuse your viewers, and you're telling a bit of a data lie.
This is where i get confused, I have the data presented regionally, with cost by month. The original formula doesn't show anything for the first month just for one region. The rest of the regions have values calculated for the first month (they are mostyly high negative values). I don't understand how those values got calculated. Is there a way to find out what data is used for the calculated value (what is the data behind it)?
I only filter for 2013 data but I also have 2012 and 2011 stored in the database (I wouldn't expect them to be used in the calculation though).
I can see what's been happening. I've had both "region" and "month" in the column space. When the value for the next region was displayed for the first month, the total value for the previous region has been deducted.
I have moved region to the row section and the values are calculated correctly.
Shawn, thanks for your help.