question

Daniel-2027 avatar image
0 Votes"
Daniel-2027 asked CarrinWu-MSFT commented

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

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])
sql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Daniel-2027, we have not get a reply from you. Could below answers help you? If yes, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered

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 <Date member for the first receipt date>: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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @Daniel-2027,

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]

136303-screenshot-2021-09-29-180545.png

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.