Create Set to get Last month of Each Year

Question

I would like to create a set that returned the last Date for each year. For example all previous years would return December, but the current year would only return the current month.

WITH 
  SET testset AS 
    NonEmpty
    (
      Generate
      (
        {
            OpeningPeriod([Date].[Calendar].[Month])
          : 
            ClosingPeriod([Date].[Calendar].Month)
        }
       ,{[Date].[calendar].CurrentMember}
      )
     ,[Measures].[Sale Amount]
    ) 
SELECT 
  NON EMPTY 
    (
      [Measures].[Sale Amount]
     ,[Date].[Year].[Year]
    ) ON 0
 ,NON EMPTY 
    [testset] ON 1
FROM [Cube]

Here is an example of a script that returns the values for each month. I've tried using tail and lastchild, but that only returns the most recent. I would like it to return for every Year.

Answer 1

@Whytheq has already given a very good solution. Treat this as an alternative. This might be a tad faster as it doesn't use the GENERATE function (not sure though!).

Have a calculated/cube measure which basically tells whether a month is the last month of the year. Then select those months out of the set of months.

with member measures.islastchild as
iif
    (
       [Date].[Calendar].currentmember is 
       [Date].[Calendar].currentmember.parent.parent.parent.lastchild.lastchild.lastchild,
       1,
       null
    )

The member measures.islastchild returns 1 if the month is the last month of the year. Else it would return null.

set lastmonths as
filter(
        [Date].[Calendar].[Month].members, 
        measures.islastchild = 1
      )

The set lastmonths is then the set you need.

edit

To further improve the performance, you can NonEmpty function instead of the iterative FILTER function.

set lastmonths as
NonEmpty(
         [Date].[Calendar].[Month].members, 
         measures.islastchild
        )

select lastmonths on 1,
{} on 0
from [Adventure Works]

EDIT 2: To get the last non month with sales

with member measures.islastnonemptychild as
iif
    (
       [Date].[Calendar].currentmember is 
       TAIL(NonEmpty([Date].[Calendar].currentmember.parent.parent.parent.lastchild.lastchild.children, [Measures].[Sale Amount])).ITEM(0),
       1,
       null
    )

set nonemptylastmonths as
NonEmpty(
         [Date].[Calendar].[Month].members, 
         measures.islastnonemptychild 
        )

Answer 2

In terms of just the last month - ignoring whether there is data for it or not the following:

WITH 
  SET [AllYears] AS 
    [Date].[Calendar].[Calendar Year].MEMBERS 
  SET [LastMths] AS 
    Generate
    (
      [AllYears] AS S
     ,Tail
      (
        Descendants
        (
          S.CurrentMember
         ,[Date].[Calendar].[Month]
        )
       ,1
      )
    ) 
SELECT 
  {} ON 0
 ,[LastMths] ON 1
FROM [Adventure Works];

Returns this:

If I want to adapt the above so that it is the last month per year that has data for a specific measure then wrap NonEmpty around the set created by Descendants:

WITH 
  SET [AllYears] AS 
    [Date].[Calendar].[Calendar Year].MEMBERS 
  SET [LastMths] AS 
    Generate
    (
      [AllYears] AS S
     ,Tail
      (
        NonEmpty //<<new
        (
          Descendants
          (
            S.CurrentMember
           ,[Date].[Calendar].[Month]
          )
         ,[Measures].[Internet Sales Amount] //<<new
        )
       ,1
      )
    ) 
SELECT 
  {} ON 0
 ,[LastMths] ON 1
FROM [Adventure Works];

It now gives us this:

We can then add in the tuple you have on rows (I have used the attribute hierarchy this time for years as [Date].[Calendar] is already in use)

WITH 
  SET [AllYears] AS 
    [Date].[Calendar].[Calendar Year].MEMBERS 
  SET [LastMths] AS 
    Generate
    (
      [AllYears] AS S
     ,Tail
      (
        NonEmpty
        (
          Descendants
          (
            S.CurrentMember
           ,[Date].[Calendar].[Month]
          )
         ,[Measures].[Internet Sales Amount]
        )
       ,1
      )
    ) 
SELECT 
  NON EMPTY 
    (
      [Measures].[Internet Sales Amount]
     ,[Date].[Calendar Year].[Calendar Year]
    ) ON 0
 ,[LastMths] ON 1
FROM [Adventure Works];

Now we get this: