Conditional formula for Calculated member

0
votes

I created calculated member in the cube something like this:

CREATE MEMBER [Custom].[Rolling 12] as Aggregate([Time].[Time].currentmember:[Time].[Time].currentmember.Lag(11), [Custom].[Frequency].defaultMember)

And it will aggregate last 12 months for any measure in the cube just fine. But I have Average Balance measure that shouldn't aggregate but average on the same period as defined above.

So, is it possible to write something like this (pseudo code):

CREATE MEMBER [Custom].[Rolling 12] as

if Measure Name = Average Balance then

Average([Time].[Time].currentmember:[Time].[Time].currentmember.Lag(11), [Custom].[Frequency].defaultMember)

else if Any Other Measure

Aggregate([Time].[Time].currentmember:[Time].[Time].currentmember.Lag(11), [Custom].[Frequency].defaultMember)

1
ssasmdx

1 Answers

2
votes

Leave initial calculation as it is (with aggregate() function).

You can write a SCOPE for this measure only:

SCOPE([Custom].[Rolling 12],[Measures].[Average Balance]);
THIS = {your custom average calculation};
END SCOPE;

Or w/o SCOPE it has to work this way:

([Custom].[Rolling 12],[Measures].[Average Balance]) = {your custom average calculation};