CODEWITHSUNDEEP
datemdxdimension
ilija veselica
ilija veselica

Reputation: 9574

MDX - calculate one date dimension from another date dimension

I have a fact table that has 2 dates Invoice Date and Accounting Current Date. In order to get requested Revenue value I need to use combination of these two dates. For example, if I need YTD Revenue I need to select it like this:

(Note: I am writing SQL query because I am more familiar with it)

SELECT Revenue
FROM
Fact_Revenue
WHERE
Invoice_Date <= '2011-10-22'
and AccountingCurrent >= '2011-01'
and AccountingCurrent <= '2011-10'

Besides Revenue, this fact tables has other information that I also need, but for calculating this other data I don't need Accounting Current Date. So my idea is to use only 1 date (Invoice Date) in main MDX query (so that I can grab as many data with 1 query as I can) and for calculating Revenue I would like to use Calculated Member and in there I would like to associate Accounting Current Date with selected Invoice Date.

For example

SELECT {[Measure].[RevenueYTD], 
[Measure].[RevenueMTD], 
[Measure].[NumberOfInvoices], 
[Measure].[NumberOfPolicies]} 
ON COLUMNS, 
{[People].Members} ON ROWS 
FROM [Cube]
WHERE
[Invoice Date].[Date Hierarchy].[Date].&[2011-10-22]

In this case, [Measure].[RevenueYTD] and [Measure].[RevenueMTD] need to be limited by Accounting Current Date and Invoice Date must be lower than the date from the query. On the other hand, I need [Measure].[NumberOfInvoices] and [Measure].[NumberOfPolicies] for particual Invoice Date (or MTD Date, whatever), but without involvemenet of Accounting Current Date

Calculated member query should do something like this (this is more like algorithm):

ROUND(
    SUM(
        YTD([Accounting Current Date].[Date Hierarchy].CurrentMember), 
        [Measures].[Revenue]
        ),
    2)
WHERE [Invoice Current Date].[Date Hierarchy] < [Invoice Current Date].[Date Hierarchy].CurrentMember

Upvotes: 1

Views: 2461

Answers (1)

ic3
ic3

Reputation: 7690

Navigating from one dimension to another is not something trivial in MDX. In theory dimensions are independent so standard language is missing functions for doing this. You can use StrToMember MDX function but it's slow and a bit strange.

For your filters, let's start with the first one :

Invoice_Date <= '2011-10-22'

In MDX we'll have to create a set with the members matching the expression. This can be done using the Range set operator :

NULL:[Invoice Date].[Date Hierarchy].[Date].&[2011-10-22]

The other filter is easy to guess :

AccountingCurrent >= '2011-01' and AccountingCurrent <= '2011-10'

MDX version :

[Accounting Date].[Date Hierarchy].[Date].&[2011-01-31]:[Accounting Date].[Date Hierarchy].[Date].&[2011-10-30]

It's also possible using Filter MDX function if your need different type of filters.

Now we need to take the pieces and build the query. One possible solution is using a set slicer and overwritting the values when you don't want the filter to be applied :

 WITH 
   // here we're changing the 'selection' from the where clause
   MEMBER [Measure].[NumberOfInvoices II] AS ([Accounting Date].[Date Hierarchy].defaultmember,[Measure].[NumberOfInvoices])
 SELECT
   .. axis here [Measure].[RevenueYTD] will be applying the filters defined in the where clause
 FROM MyCube
 WHERE {[Accounting Date].[Date Hierarchy].[Date].&[2011-01-31]:[Accounting Date].[Date Hierarchy].[Date].&[2011-10-30]}

Upvotes: 1

Related Questions