How can I show the last opening period for each project in my project dimension hierarchy using MDX ( Tail function)?

Question

I have two attributes hierarchies in my dimension [Dim Project]:

• Project Code

• Opening date

Project A with 3 opening date : 2023-01-01 , 2023-01-02 and 2023-01-03

Project B with 2 opening date : 2023-01-02 , 2023-01-04

MDX query :

------------

SELECT 
    [Dim Project].[Project Code].[Project Code].ALLMEMBERS
  * 
    [Dim Project].[Opening date].[Opening date].ALLMEMBERS ON 0
 ,[Measures].[Measure1] ON 1
FROM Mycube;

I want to show for every Project its last Opening Period.

Project_Code Opening_Period Measure1

---

Project A 2023-01-03 Measure1_1

Project B 2023-01-04 Measure1_2

How can write this in MDX.? Thank you so much for your help!

I know that Tail function can help in this case but how !

Answer 1

GENERATE is a function that can iterate through members - following is not tested but might help:

SELECT 
  Generate
  (
    [Dim Project].[Project Code].[Project Code].MEMBERS
   ,
      [Dim Project].[Project Code].CurrentMember
    * 
      Tail
      (
        NonEmpty
        (
          [Dim Project].[Opening date].[Opening date].MEMBERS
         ,([Dim Project].[Project Code].CurrentMember, [Measures].[Measure1])
        )
      )
  ) ON 0
 ,[Measures].[Measure1] ON 1
FROM Mycube;

Testing similar logic against MS's sandpit cube Adventure Works it seems ok:

SELECT 
  Generate
  (
    [Product].[Category].[Category].members
   ,
      [Product].[Category].CurrentMember
    * 
      Tail
      (
        NonEmpty
        (
          [Date].[Date].[Date].MEMBERS
         ,([Product].[Category].CurrentMember,[Measures].[Sales Amount])
        )
      )
  ) ON 1
 ,[Measures].[Sales Amount] ON 0
FROM  [Adventure Works];