MDX: Filter on Dates Before Today

Apologies with the generalisation & simplified nature of this case. I have to protect the identity of the company and make it generic so that it is applicable for a wider audience.

The Issue

A few days ago somebody came to me with a case where he had a cube with a customer dimension. The customer dimension had two attributes called Start Date and End Date. He wanted to filter the customer dimension where the Start Date is <= today and the End Date is >= today. Both the Start Date and End Date attributes are in the form of [YYYYMMDD], an integer, which is an FK to the key of the date dimension.

Say today is 26^th Oct 2010. You want to do something like:

select [Measures].[Measure1] on 0 from [Cube]
where
( [Customer].[Start Date].FirstChild:
  [Customer].[Start Date].&[20101016]
);

where the 20101016 is the latest date before today that exist in the end date column.

This is because if you type today’s date like this (26^th):

select [Measures].[Measure1] on 0 from [Cube]
where
( [Customer].[Start Date].FirstChild:
  [Customer].[Start Date].&[20101026]
);

the query will return nothing because in the dim customer table there is no end date = 26^th Oct 2010.

Create The Cube

Let’s create the tables, and then create the cube. We don’t need to create the date dimension. We just have to create 1 measure group and 1 dimension (customer) which is linked directly to the measure group.

-- create tables
create table f1
( customer_key int,
  measure1 numeric(20,4)
)

create table dim_customer
( customer_key int,
  start_date_key int,
  end_date_key int
)

-- populate tables
truncate table dim_customer
insert into dim_customer values (1, 20101005, 20101010)
insert into dim_customer values (2, 20101011, 20101015)
insert into dim_customer values (3, 20101016, 20101020)

truncate table f1
insert into f1 values (1, 10)
insert into f1 values (2, 20)
insert into f1 values (3, 30)

select c.customer_key, c.start_date_key, c. end_date_key, f1.measure1
from f1 join dim_customer c on c.customer_key = f1.customer_key

Output:
1  20101005   20101010   10.0000
2  20101011   20101015   20.0000
3  20101016   20101020   30.0000

Now create the cube in BIDS. Just create 1 dimension, which is dim customer. No need to create the date dimension. Like this:

Query The Cube

Deploy and process the cube then query it:

select [Measures].[Measure1] on 0 from [Cube]
where ([Customer].[End Date].&[1]:[Customer].[End Date].&[20101015]);
--output: 30 (there is a row in dim_customer with end date = 15/10/10)

select [Measures].[Measure1] on 0 from [Cube]
where ([Customer].[End Date].&[1]:[Customer].[End Date].&[20101016]);
--output: null (there isn't a row in dim_customer with end date = 16/10/10

select [Measures].[Measure1] on 0 from [Cube]
where ([Customer].[End Date].&[1]:[Customer].[Start Date].&[20101005]);
--output: 10 (there isn't a row in dim_customer with start date = 5/10/10

select [Measures].[Measure1] on 0 from [Cube]
where ([Customer].[End Date].&[1]:[Customer].[Start Date].&[20101008]);
--output: null (there isn't a row in dim_customer with start date = 8/10/10

The Solution

The solution is like this:

select [Measures].[Measure1] on 0
from [Cube]
where
( [Customer].[Start Date].[All].FirstChild:
  tail
  ( filter
    ( [Customer].[Start Date].[All].Children,
      [Customer].[Start Date].CurrentMember.Name <= Format(Now(), "yyyyMMdd")
    ), 1
  ).item(0)
);

Explanation:

First we get today’s date using now() function. Then we format the date to yyyyMMdd. Then we get all members of the Start Date attribute which is smaller or equal to today. This is done by comparing the current member of the start date attribute with today’s date. Then we take the last member using tail function. Then we convert from set to member using item(0).

Same principles apply for the end date.

Hope this helps, Vincent Rainardi 26^th Oct 2010. As always I’d welcome question and discussion on vrainardi@gmail.com.