MDX - Get first Sales Date that is greater than or equal to their First Receipt Date for each Item Number

Question

The query below pulls the first sale date for each item number.

Unfortunately, sales for an item number can accidentally be processed before we physically have the product in stock. That incorrect transaction stays on record, and thus the first sales date given by my query wouldn't be accurate for those items.

We have a field titled [Item].[First Receipt Date].[First Receipt Date] where I believe the format of the [First Receipt Date] is (varchar(10), null) and a member would look like [Item].[First Receipt Date].&[1998-03-01]

I would like to update my query to get the first sales date that is greater than or equal to the [First Receipt Date].

WITH
MEMBER Measures.[First Sale] AS 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 Code].&[USA]&[746256],
[Item].[Item Code].&[USA]&[736808] } ) ON COLUMNS
FROM [DATABASE])

Answer

As an MDX exercise, you'll need to fine-tune your set of Date members to exclude dates prior to the first receipt date of the current item. The general idea would be :NULL, and there could be quite an effort in coding that member as a reference to the [Item].[First Receipt Date] member, depending on the exact datatypes and formats of the elements involved.
A preferred solution to this issue, though, is cleansing the data before it even reaches the cube. Surely incorrect transactions don't belong there? Or if they somehow do, don't they deserve some kind of a flag to easily mask them?

Answer

Hi @Daniel ,

Thanks for your posting.

I use IIF () function to compare the 2 type of date, please refer to below sample MDX:

WITH   
MEMBER [Measures].[First Delivery Date] AS  
       FILTER([Delivery Date].[Calendar].[Date].MEMBERS  
            , [Measures].[Reseller Order count]).ITEM(0).NAME  
member [measures].[First sales date] as  
filter([Ship Date].[Date].[Date].members  
,[Measures].[Reseller Order count]).ITEM(0).NAME  
member [measures].[CompareDate] as   
IIF(  
[Measures].[First Delivery Date]>[measures].[First sales date],1,2  
)  
  
   SELECT {[Measures].[Reseller Order count]  
         , [Measures].[First Delivery Date]  
 ,[measures].[First sales date]  
 ,[measures].[CompareDate]  
       } ON 0,  
     TOPCOUNT([Employee].[Employees].MEMBERS,10) ON 1  
   FROM [Adventure Works]

If I misunderstood your question, please feel free to let me know.

Best regards,
Carrin

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

MDX - Get first Sales Date that is greater than or equal to their First Receipt Date for each Item Number

2 answers