Forum Discussion
Using Previous Row Value in formula
- Anonymous8 years ago
I was able to get around my issue by using the following formula in my calculated column:
YOY Fuel Economy Rate with Degradation (MPG) =
Var One = RELATED(FactsVehicle[Fuel Economy Rate]) * (1-Fact1[M_Annual Fuel Degradation])
Var Two = One * (1-Fact1[M_Annual Fuel Degradation])
Var Three = Two * (1-Fact1[M_Annual Fuel Degradation])
Var Four = Three * (1-Fact1[M_Annual Fuel Degradation])
Var Five = Four * (1-Fact1[M_Annual Fuel Degradation])
Var Six = Five * (1-Fact1[M_Annual Fuel Degradation])
Var Seven = Six * (1-Fact1[M_Annual Fuel Degradation])
Var Eight = Seven * (1-Fact1[M_Annual Fuel Degradation])
Var Nine = Eight * (1-Fact1[M_Annual Fuel Degradation])
Var Ten = Nine * (1-Fact1[M_Annual Fuel Degradation])
RETURN
SWITCH(FactsVehicleYearly[Year],
1, One, 2, Two, 3, Three, 4, Four, 5, Five, 6, Six, 7, Seven, 8, Eight, 9, Nine, 10, Ten, 0)
I was able to get around my issue by using the following formula in my calculated column:
YOY Fuel Economy Rate with Degradation (MPG) =
Var One = RELATED(FactsVehicle[Fuel Economy Rate]) * (1-Fact1[M_Annual Fuel Degradation])
Var Two = One * (1-Fact1[M_Annual Fuel Degradation])
Var Three = Two * (1-Fact1[M_Annual Fuel Degradation])
Var Four = Three * (1-Fact1[M_Annual Fuel Degradation])
Var Five = Four * (1-Fact1[M_Annual Fuel Degradation])
Var Six = Five * (1-Fact1[M_Annual Fuel Degradation])
Var Seven = Six * (1-Fact1[M_Annual Fuel Degradation])
Var Eight = Seven * (1-Fact1[M_Annual Fuel Degradation])
Var Nine = Eight * (1-Fact1[M_Annual Fuel Degradation])
Var Ten = Nine * (1-Fact1[M_Annual Fuel Degradation])
RETURN
SWITCH(FactsVehicleYearly[Year],
1, One, 2, Two, 3, Three, 4, Four, 5, Five, 6, Six, 7, Seven, 8, Eight, 9, Nine, 10, Ten, 0)