I have hierachical sales (10 years history) and costs data (2/3/4 years history) at the following levels. Im trying to create a calculated field "PROFIT" for only the years that i have sales and costs data bearing in mind some products have 2 or 3 or 4 years historical costs data. How would i write a formula to
1) Create profitability by year which reflects the amount of data is available. Ie if there is 2 years costs data,id like to zee 2 years profitability. If there is 4 years costs, Id like to see 4 years profitability and see the other years blanked out
2) Be able to measure profitability at the each of the hierachical levels below
SALES: 10 years of Sales data with following levels
2) Franchise (which contains multiple brands)'
3) Business Unit which contains multiple franchises)
4) Country (Which contains multiple Business Units)
COSTS: 2 years of Costs data which is only displayed at