MDX测量仅显示过去12个月 [英] MDX Measure to show only Last 12 Months

问题描述

朋友你好，

我需要一个衡量标准才能显示过去12个月的收入。对于所有其他月份，它应为null /空白/空...

我创建了一个动态命名集：[Last_12_Months]

tail（非空（[REV DATE DIM]。[Date_Hier]。[Month] .members，[Measures]。[Amount]），12）

然后，我创建了一个度量：

总和（[Last_12_Months]，[Measures]。[Amount]）

但它导致给我的每个12个月的TOTAL几个月。

我需要每个月获得一个月收入，而不是总共12个。

我主要使用DAX所以看起来像它正在做ALL（），请帮我MDX

谢谢

Michael

---

Michael

解决方案

Hi M.Shparber，

根据你的描述，似乎你想显示特定月份（过去12个月）的值和其他月份显示0或null，对吗？如果我有什么误解，请纠正我。

 WITH MEMBER [Measures]。[New Measure] as 
 iif（cdate（[Date]。[日历日期] .currentmember.MemberValue）< CDATE（尾部（[日期]。[日历日期]。[月名]，1）。项目（0）.LAG（11）.MEMBER_VALUE），0，[测量]。 [互联网销售额]）
 
 SELECT [Measures]。[New Measure] ON 0，[Date]。[Calendar Date]。[Month Name] ON 1 
 FROM [Analysis Services Tutorial] ] 
 
 
 

最好的问候，

Zoe Zhi

Hello friends,

I need a measure to show revenues for 12 last months only. For all other months it should be null/blank/empty...

I have created a Dynamic Named Set: [Last_12_Months]

tail(nonempty([REV DATE DIM].[Date_Hier].[Month].members, [Measures].[Amount]),12)

Then, I created a measure:

sum([Last_12_Months],[Measures].[Amount])

But it results in giving me the TOTAL of all 12 last months for each one of the months.

I need to get a monthly revenue for each month, not a total of 12.

I mostly use DAX so it looks like it is doing ALL(), please help me with MDX

Thank you

Michael

---

Michael

解决方案

Hi M.Shparber,

According to your description, it seems that you want to show specific month's(last 12 month) value and other month show 0 or null, right? If I have something misunderstood, please correct me.

WITH MEMBER [Measures].[New Measure] as
 iif(cdate([Date].[Calendar Date].currentmember.MemberValue)<CDATE(tail([Date].[Calendar Date].[Month Name],1).Item(0).LAG(11).MEMBER_VALUE),0,[Measures].[Internet Sales Count])

SELECT  [Measures].[New Measure] ON 0,[Date].[Calendar Date].[Month Name] ON 1
  FROM [Analysis Services Tutorial]

Best Regards,
Zoe Zhi

这篇关于MDX测量仅显示过去12个月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！