Trailing Three Months (T3M) MDX formula in Essbase
Summary
Trailing Three Months (T3M) MDX formula in EssbaseContent
Our T3M formula (below) does not give a consistent result. It works for some but others. Can you shade some light?
Our Period and Year are a seperate dimension. The formula is automated using a substitution variable ClosedMo. T3M has the highest solve order.
CASE
WHEN [Jan] = [&ClosedMo]
THEN ([Dec],[Years].CurrentMember.Lag(1)) + ([Nov],[Years].CurrentMember.Lag(1)) + ([Oct],[Years].CurrentMember.Lag(1))
WHEN [Feb] = [&ClosedMo]
THEN ([Jan],[Years].CurrentMember) + ([Dec],[Years].CurrentMember.Lag(1)) + ([Nov],[Years].CurrentMember.Lag(1))
WHEN [Mar] = [&ClosedMo]
THEN ([Feb],[Years].CurrentMember) + ([Jan],[Years].CurrentMember) + ([Dec],[Years].CurrentMember.Lag(1))
0