Using Previous Row Value in formula

[Anonymous]

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?

[Anonymous · Accepted solution]

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)

[Greg_Deckler]

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)

[Anonymous]

Thanks for the info. How am I going to get around my circular dependency issue (since I am trying to pull a value from the same column my formula is in)?