Creating an Index with a calulated member in MDX ?

Anyone got a clue on how to create a meassure that shows the Actual meassure as an Index, where current month is index 100 ?

Ex:

Jan07: Actual = 32000 -> index = 114

Feb07: Actual = 34000 -> index = 121

Mar07: Actual = 28000 -> index = 100

Apr07: Actual = 20000 -> index = 71

It must be something with creating af defaultmember in the timedimension, that is dynamic and points to getdate(). Then make a calculation that uses defaultmembers value to calculate the indexnumber|||

If you wanted to use the current system date on the server as your definition of the "current date" then you could do something roughly like the following:

Code Snippet

(

([Date].[Month].CurrentMember, [Measures].[Actual])

/ (StrToMember("[Date].[Month].[" + FORMAT(NOW(),"MMMyy") + "]"),[Measures].[Actual])

) * 100

This code is pretty rough, there is no logic in there for handling the All member and it would only work at month granularity, but hopefully it is enough to get you started. You could also set the "current date" as the default member, but it is not strictly necessary to do the calculation.