MDX每年的最后一个月返回 [英] MDX return last month of every year
问题描述
我正在尝试创建一个集合,以返回包含每年数据的最后一个月的值.
例如,如果我将年份放在行上
2011、2012、2013、2014将全部包含12月的数据.
2015年将包含2015年6月的数据.
I am trying to create a set to return the value of the last month that contains data for each year.
For example if I would put year on the rows
2011,2012,2013,2014 would all contain data for December.
2015 would contain data for June of 2015.
除了最近的一个月,我似乎什么也没得到.我认为这是因为我的尾巴陈述,但我不确定如何解决它.
I can't seem to get anything but the latest month to return. I figure it is because of my tail statement, but I'm not sure how to fix it.
CREATE SET [Last Statement Month] AS
Tail(
nonempty(
Descendants(
[Date].[Calendar].currentmember
,[Date].[Calendar].[Month]
),
[Measures].[Sale Amount]
), 1);
我还尝试获取每个月的最后一天,但是当我将其与年份一起使用时,则没有任何显示.
I also tried to get the last day of each month, but when I use this with the year on the rows nothing shows up.
GENERATE(
{
Openingperiod([Date].[Calendar].[Month]):ClosingPeriod([Date].[Calendar].Month)
},
{[Date].[Calendar].CurrentMember.Lastchild}
);
推荐答案
我目前不在AdvWrks范围内,因此无法进行测试.以下帮助吗?
I'm currently away from AdvWrks so unable to test. Does the following help?
CREATE SET [Last Statement Month] AS
TAIL(
NONEMPTY(
EXISTING ([Date].[Calendar].[Month].MEMBERS)
,[Measures].[Sale Amount]
)
);
(如果此方法有效),如果最后执行EXISTING
,性能显然会更好:
(If this approach works) Performance is apparently better if EXISTING
is performed last:
CREATE SET [Last Statement Month] AS
TAIL(
EXISTING
NONEMPTY(
[Date].[Calendar].[Month].MEMBERS
,[Measures].[Sale Amount]
)
);
看起来上面的方法行不通.我在下面添加了一个替代方案,它可能是您正在寻找的更多方案:
Looks like the above isn't going to work. I've added an alternative in the following which maybe is more what you're looking for:
WITH
DYNAMIC SET [Last Statement Month] AS
Tail
(
NonEmpty
(
(EXISTING
[Date].[Calendar].[Month].MEMBERS)
,[Measures].[Internet Sales Amount]
)
)
MEMBER [Measures].[x] AS
[Last Statement Month].Item(0).Item(0).Member_Caption
MEMBER [Measures].[Lst mth with data] AS `<<<<maybe something like this helps?
Max
(
(EXISTING
[Date].[Calendar].[Month].MEMBERS)
,IIF
(
[Measures].[Internet Sales Amount] = 0
,NULL
/*,[Date].[Calendar].CurrentMember.Member_Caption*/ //<<WRONG PROPERTY USED
,[Date].[Calendar].CurrentMember.MemberValue //<<should help!!
)
)
SELECT
{[Measures].[Lst mth with data],[Measures].[x]} ON 0
,[Date].[Calendar].[Calendar Year] ON 1
FROM [Adventure Works];
结果:
编辑后返回以下内容:
这篇关于MDX每年的最后一个月返回的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!