Rolling last 3 month average column in Spotfire

Question

I'm trying to calculate for rolling average last 3 months . i'm stuck up to this calculation and could not figure out on how to inject this in spotfire using the logic.

the first month (feb) will use the recovery% as multiplier for wip% + recovery%

for the second month (mar) will get the average of (feb) and multiply to wip% +recovery%

for the 3rd month (apr) will get the average of (feb ,mar) and multiply to wip% + recovery%

for the 4month (may) will get the avg of (feb,mar,apr) and multuply to wip% +recovery%

my ultimate goal is to create a column for rolling average 3 months to be use in barchart and cross table. any help is very much appreciated. Thank you.

attached sample table

Answer 1

using below code it's giving me different result. there are no data for the first 2 month (Feb and Mar) and also the average i'm getting for the 3 months rolling is including the current month which the 3months rolling will start from the previous month.

(Sum([WIP]) / Sum([Assy Input]) * Sum([Recover]) / Sum([Input])) + (Sum([Recover]) / Sum([Input]))
THEN Avg([Value]) OVER (LastPeriods(3,[Axis.Rows])) THEN If(Count() OVER (LastPeriods(3,[Axis.Rows]))=3,[Value],null) as [3 mos Rolling]