MDX Get minimum sales date for each item number

Question

The query below pulls all dates with sales for each item number

I strictly want the minimum sales date for each item number, not all of them.

Example:

I have tried using MIN in multiple ways but could not get it to work. Any help is appreciated.

SELECT NON EMPTY   
{ [Measures].[Sales Units] } ON COLUMNS,  
  
NON EMPTY   
{ CROSSJOIN(([Item].[Item Number].[Item Number].ALLMEMBERS ),  
FILTER( [Date].[Date].[Date].AllMembers, [Date].[Date] >= [Item].[First Date Received]))}   
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS   
  
FROM ( SELECT ( { [Item].[Item Number].&[USA]&[123456], [Item].[Item Number].&[USA]&[654321] } ) ON COLUMNS  
FROM [Database])   
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS  

Answer 1

If you have this as a date column in your sales table the easiest way to do this is to go into your cube design and create a measure with the MIN aggregation type over that date column. Then you could just use that measure along with the item number attribute in your query.

The other option would be a measure like the following, but the nonempty scan over the date members at run time will be more expensive than creating a measure.

WITH
MEMBER Measures.[First Sale] NONEMPTY( [Date].[Date].[Date].Members, Measures.[Sales Units] ).item(0).item(0).Name
SELECT
{ Measures.[First Sale] } on COLUMNS,
[Item].[Item Number].[Item Number].ALLMEMBERS ON ROWS
FROM ( SELECT ( { [Item].[Item Number].&[USA]&[123456], [Item].[Item Number].&[USA]&[654321] } ) ON COLUMNS
FROM [Database])