1 of 1 people found this helpful
I would suggest making a separate worksheet for the $ and % change between the two years. Have you got a workbook with your sample data for us to work with?
I have attached a workbook with the above example using the superstore data. The variance calculation looks at comparing 2014 and 2015 years.
How can I just reference specific columns within the workbook in a field? I have not figured out how to just compare October vs September. The data I am working with is confidential and cannot be shared unfortunately.
The table calculation you need is LOOKUP, and you'll use the first() and last() functions within it. I'm not sure if your numbers are profit, sales, or something else, so I'll just call them profit in this example, but make sure to change that to whatever field name you're using.
For your $ Var calc - let's call it [$ Var]:
lookup( [profit], first()) - lookup( [profit], last())
For your % Var calc:
[$ Var] / lookup( [profit], first())
Make sure both table calculations are set to calculate using Table (across) and that'll do it.
Let me know if you have any questions.