0

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

I have the following MDX script:

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];

It results in the following:

enter image description here

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.

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

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

whytheq
  • 34,466
  • 65
  • 172
  • 267
  • Do you really want to have data for the first to the 11th of each month, and display the month in the rows? – FrankPl Oct 14 '14 at 07:56
  • @FrankPl Yes - I'm aiming for `Month` in column A, then `Total` for month in column B, then `PrevEquivalentMTD` in column c (this would be sum of first X number of days in each month, where X is equal to number of days completed in most recent month). @FrankPI - this is a better attempt I've made at cracking this: http://stackoverflow.com/questions/26349108/how-to-find-simple-approach-to-equivalent-set-days-in-previous-months – whytheq Oct 14 '14 at 08:17

1 Answers1

1

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]

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.

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
    )

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.

FrankPl
  • 13,205
  • 2
  • 14
  • 40
  • +1 thanks Frank - this request initially seemed quite trivial and a day later and I'm still scratching my head. Maybe need to move Calendar hier into a named set > then how about a custom measure that uses `.count` against the leaves of the last item in the custom set's tail > then feed that measure into your head function to replace the 11 ? – whytheq Oct 14 '14 at 10:46