Forum Discussion
Using Previous Row Value in formula
I have two tables. FactsVehicleYearly:
Facts1:
I have created a measure in Facts1 (as seen in the formula bar of the previous image) to use in my formula in the FactsVehiclesYearly table. I am trying to calculate a YOY Fuel value for each row in the FactsVehicleYearly table. Here is the formula below:
YOY Fuel Economy Rate with Degradation (MPG) = IF(FactsVehicleYearly[Year] = 1, RELATED(FactsVehicle[Fuel Economy Rate])*(1-Fact1[M_Annual Fuel Degradation]), I NEED HELP HERE)
If Year is not equal to one, the formula should go something like this:
Previous YOY Fuel Economy Rate with Degradation * (1-Fact1[M_Annual Fuel Degradation])
I'm stuck on how to get the previous YOY Fuel value. Any ideas?
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)
3 Replies
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)So, take a look at my quick measure here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293
I realize it is a completely different issue, but I believe it demonstrates the basic technique you will need to use. Note where I am accounting for things happening in different years (week spans years)
