You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

Trailing Three Months (T3M) MDX formula in Essbase

Received Response
8
Views
1
Comments
edited Aug 2, 2018 2:36AM in Essbase 1 comment

Summary

Trailing Three Months (T3M) MDX formula in Essbase

Content

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))

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!