MDX return last month of every year

Question

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}
);

whytheq whytheq · Accepted Answer · 2015-06-29T20:05:08

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

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

Results in this:

enter image description here

After Edit returns this:

enter image description here

MDX return last month of every year

1 Answers