MTD for current month in last year

Question

How I can calculate MTD for current month in last year? Below query returns total [Net Sales Amount] for 12.2015, but need to have sales from 01.12.2015 to 09.12.2015(Today).

   SUM(
        MTD(
            ParallelPeriod(
                [Calender].[YMD].[Month],
                12,
                [Calender].[YMD].CurrentMember
            )
        )
        ,[Measures].[Net Sales Amount]
    )

Answer 1

I think you need to use HEAD of the member you're finding:

SUM(
    HEAD(
        ParallelPeriod(
            [Calender].[YMD].[Month],
            12,
            [Calender].[YMD].CurrentMember
        ).CHILDREN,
    , 9
    )
    ,[Measures].[Net Sales Amount]
)

The above is assuming that in the design of your cube Dates are the children of Month.

You need to make the 9 dynamic - do you have future dates in your cube?

If you do not have future dates then this could work:

WITH 
  MEMBER [Measures].[NumDaysInCurrentMonth] AS
       Count(
          Descendants(
             TAIL([Date].[Calendar].[Month]).Item(0) //<<<not sure if Item(0) is required
            ,[Date].[Calendar].[Date]
            ,SELF
          )
        )

If you do have future dates then maybe the following:

WITH 
  MEMBER [Measures].[NumDaysInCurrentMonth] AS
       count(
          NONEMPTY(
            Descendants(
               TAIL([Date].[Calendar].[Month]).Item(0) //<<<not sure if Item(0) is required
              ,[Date].[Calendar].[Date]
              ,SELF
            )
          )
        )

Then one of the above can feed into the previous:

WITH 
  MEMBER [Measures].[NumDaysInCurrentMonth] AS
     COUNT(
        Descendants(
           TAIL([Date].[Calendar].[Month]).Item(0) //<<<not sure if Item(0) is required
          ,[Date].[Calendar].[Date]
          ,SELF
        )
      )
  MEMBER [Measures].[PrevYearMTD] AS
     SUM(
        HEAD(
            ParallelPeriod(
                [Calender].[YMD].[Month],
                12,
                [Calender].[YMD].CurrentMember
            ).CHILDREN,
        , [Measures].[NumDaysInCurrentMonth]
        )
        ,[Measures].[Net Sales Amount]
      )