1 Reply Latest reply on Jun 25, 2013 7:41 AM by Mark Holtz

# How can I use a formula to determine which measure to use?

I'd like to use a parameter in a calculation but I want that parameter to use different parameters based on the age of a unit.

Something like this:

('[Cost Year '+str(year(today())-[UnitYear])+']')

So if I have parameters named Cost Year 1 through Cost Year 10 and the Unit Year is something like 2011 that would mean I want the value of Cost Year 2 because 2013-2011 = 2.

I can't determine if there is any way to do this type of conditional parameter lookup.

• ###### 1. Re: How can I use a formula to determine which measure to use?

I'm guessing without seeing what your underlying data looks like, but I assume you are saying you want to "swap measures" based on the comparison of each record's [Unit Year] compared to today...

So instead of creating a "lookup" as you put it, I think you just have to build a calculated field that uses an if statement against the [Unit Year] to determine which field to use as the measure field.

For example, I am assuming you have 10 different columns that show CostYear1, CostYear2, CostYear3--each being a measure.

Create your [MeasureToUse] field as:

IF YEAR(TODAY())-[Unit Year] = 1 THEN [CostYear1]
ELSEIF YEAR(TODAY())-[Unit Year] = 2 THEN [CostYear2]
ELSEIF YEAR(TODAY())-[Unit Year] = 3 THEN [CostYear3] ...
END

Does that help? If not, could you post a packaged workbook with your example?