In MDX, how can I find the quantity in transit on the last day of each month?

Question

I'm using MDX and Microsoft SQL Server 2008 Analysis Services. I have a Shipments cube with the following dimensions:

* Measures: Quantity
* Time: Year/Month/Day
* Ship Date: Year/Month/Day
* Receipt Date: Year/Month/Day

I want to know the quantity in transit on the last day of each month. How can I do that? In SQL, it would be something like this:

SELECT
  A.[Month], SUM(B.[Quantity]) AS [In Transit]
FROM
  ( SELECT [Month], MAX([Day]) AS [End of Month] FROM [Time] GROUP BY [Month] ) A
  CROSS JOIN
  ( SELECT [Quantity], [Ship Date], [Receipt Date] FROM [Shipments] ) B
WHERE
  B.[Ship Date] <= A.[End of Month]
  AND B.[Receipt Date] > A.[End of Month]
GROUP BY
  A.[Month]

Is your ship date a full date dimension (no date missing in it)? What MDX query have you tried so far? — michele

FrankPl FrankPl · Accepted Answer · 2013-10-08T18:12:58

The following query runs in AdventureWorks, i. e. the syntax is correct. AdventureWorks has a very similar structure to your cube. The main change for your cube would be the adaptation of the measure, hierarchy, and cube names; and as you did not state these all in detail, I left the query so that it runs in AdventureWorks:

with member [Measures].[In Transit] as
     Sum((null : LinkMember([Date].[Calendar].CurrentMember, [Ship Date].[Calendar]))
          *
         (LinkMember([Date].[Calendar].CurrentMember.NextMember, [Delivery Date].[Calendar]) : null),
         [Measures].[Internet Sales Amount]
        )
select { [Measures].[In Transit] }
       on columns,
       [Date].[Calendar].[Month].Members
       on rows
from [Adventure Works]

LinkMember gets a corresponding member from a hierarchy with the same structure, typically a role playing dimension based on the same dimension table. And the null : <some member> and <some member> : null constructs return the set of all members up to <some member>, or all members starting from <some member> to the end of the same level, respectively.

In MDX, how can I find the quantity in transit on the last day of each month?

1 Answers