多个月以前的等效MTD [英] Previous Equivalent MTD Across Multiple Months

问题描述

如果今天的日期是2008年7月11日，则MTD的总和为{2008年7月1日-2008年7月11日}.
上一个等效的MTD期间为六月，即{2008年6月1日至2008年6月11日}.
上一个等效的5月MTD期间为{2008年5月1日-2008年5月11日}.

If today's date is 11 July 2008 then the MTD sum is for the range {01 July 2008 - 11 July 2008}.
The previous equivalent MTD period in June is {01 June 2008 - 11 June 2008}.
The previous equivalent MTD period in May is {01 May 2008 - 11 May 2008}.

我有以下MDX脚本:

WITH 
  MEMBER [Measures].[PrevEquivalentMTD] AS 
    //SUM
    (
      ParallelPeriod
      (
        [Date].[Calendar].[Month]
       ,1
       ,[Date].[Calendar].CurrentMember
      )
     ,[Measures].[Internet Sales Amount]
    ) 
  MEMBER [Measures].[PrevEquivalentMTD_v2] AS 
    Sum
    (
      Generate
      (
        {
            [Date].[Calendar].[Date].&[20080701]
          : 
            [Date].[Calendar].[Date].&[20080710]
        }
       ,{
          ParallelPeriod
          (
            [Date].[Calendar].[Month]
           ,1
           ,[Date].[Calendar].CurrentMember.Item(0)
          )
        }
      )
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {
      [Date].[Calendar].[Month].&[2005]&[6]
    : 
      [Date].[Calendar].[Month].&[2008]&[7]
  } ON ROWS
 ,{
    [Measures].[Internet Sales Amount]
   ,[Measures].[PrevEquivalentMTD]
   ,[Measures].[PrevEquivalentMTD_v2]
  } ON COLUMNS
FROM [Adventure Works];

结果如下:

度量PrevEquivalentMTD不是我想要的，因为它返回的是前一个月的总数，而不仅仅是前10天.

The measure PrevEquivalentMTD is not what I'm looking for as it is returning the total for the previous month rather than just the first 10 days.

度量PrevEquivalentMTD_v2我不确定该怎么做才能使每个月返回相同的数字.

The measure PrevEquivalentMTD_v2 I'm unsure what this is doing to return the same number for each month.

我该如何创建一个指标来返回每月前10天的互联网销售总额?

How do I create a measure that will return the sum of the internet sales for the first 10 days of each month?

推荐答案

The PeriodsToDate方法浮现在脑海.但是由于您没有11位作为查询中的选定成员，因此我仅使用11位作为给定输入.

The PeriodsToDate method would come to mind. But as you do not have the 11th as a selected member in your query, I am just using 11 as a given input.

WITH 
  MEMBER [Measures].[PrevEquivalentMTD] AS 
    Sum(Head([Date].[Calendar].CurrentMember.PrevMember.Children, 11),
        [Measures].[Internet Sales Amount])
SELECT 
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[PrevEquivalentMTD]
  } ON COLUMNS,
  {
      [Date].[Calendar].[Month].&[2005]&[6]
    : 
      [Date].[Calendar].[Month].&[2008]&[7]
  } ON ROWS
FROM [Adventure Works]

这是从单元格的行上下文中获取当前一个月前(PrevMember)前一个月的前十一个(Head( ,11))子代，然后对它们进行求和.

This is taking the first eleven (Head( ,11)) children of the month before (PrevMember) the current one from the row context of the cell, and then summing across these.

要动态进入11，您可以e. G.使用类似的东西

To get to the 11 dynamically, you could e. g. use something like

Rank(Tail([Date].[Calendar].[Date]).Item(0), 
     Tail([Date].[Calendar].[Month]).Item(0).Item(0).Children
    )

决定日历层次结构的最后一天在其父月份的子级中的位置(Rank).假设没有失踪的日子e. G.对于周末，这应该工作.对于Adventure Works，它将返回30，因为2010年11月30日是日历层次结构中的最后一天.

which determines the position (Rank) of the last day of the Calendar hierarchy within its parent month's children. Assuming there are no missing days e. g. for weekends, this should work. It returns 30 for Adventure Works, as November, 30, 2010 is the last day in the Calendar hierarchy there.

这篇关于多个月以前的等效MTD的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！