date - 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.

标签： datessasmdxcubemeasure

Answer 1

欢迎来到 MDX。要解决这个问题，您需要对“User Hierarchy”有基本的了解。另外，您应该了解“currentmember”、“parent”、“lag()”和“lastchild”的功能。

基于此，请遵循以下示例。我正在尝试在 2013 年重新制作您在 AdventureWorks 上的示例。

让我们先看看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]

结果

现在让我们做一个测量，得出上一季度最后一个月的值

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]

结果