mdx query to calculate difference between measure at nearest quarter date and measure today

Question

I am trying to calculate a measure which is the difference between a measure (say, profit) today and the nearest preceding quarter.
E.g.: if the quarter months are March, June, September and December, then:

If the current month is May, then the calculated
measure = Profit(May)-Profit(March)

If the current month is November, then the calculated
measure = Profit(November)-Profit(September)

If the current month is December, then the calculated
measure = Profit(December)-Profit(September)

I'm new to MDX and would really like some help on this.

I'm aware of .CurrentMember.PrevMember to get the previous member, but that doesn't help if you don't know how many previous members calls you should use.

Answer 1

Welcome to MDX. To solve the problem you need to have basic understanding of "User Hierarchy".Plus you should know how the functions "currentmember", "parent", "lag()" and "lastchild".

Based on this follow the example below. I am trying to re-produce your example on AdventureWorks for year 2013.

Lets first see all the internet sales amount for 2013

select
{[Measures].[Internet Sales Amount]}
on columns,
{
[Date].[Month of Year].[Month of Year]
}
on rows from 
[adventure works]
where 
[Date].[Calendar Year].&[2013]

Result

Now lets make a measure that will result value for the last month of previous quater

with 
member [Measures].[LastMonthOfPreviousQuater] as 
([Date].[Calendar].currentmember.parent.lag(1).lastchild,
[Measures].[Internet Sales Amount])

select
{[Measures].[Internet Sales Amount],[Measures].[LastMonthOfPreviousQuater]}
on columns,
{
[Date].[Calendar].[Month].&[2013]&[09]
}
on rows from 
[adventure works]

Result