Calculated member expression in SSAS 2008

Question

I need to create a calculated member to a cube to show me the total amount due invoices for each customer less than or equal to 60 days.

I understand that I can find the current date minus the date of the invoice and, if the result in days, is less than or equal to 60, then make the sum of all invoices for each customer.

I don't khow how implement this into the expression for a calculated member. I have done this, but I get error in the SELECT and I don't know why:

CREATE MEMBER CURRENTCUBE.[Measures].[Importe60]
AS Sum([Measures].[Importe de la factura])

SELECT [Measures].[Importe60] ON COLUMNS,
[Clientes].[Nombre].Members ON ROWS
FROM CuboVentas
WHERE DateDiff(dd,[Facturas].[Fecha Factura],getDate())<=60,

VISIBLE = 1, ASSOCIATED_MEASURE_GROUP = 'Facturas clientes';

Does anyone know what I'm doing wrong?

Answer 1

The problem is with your WHERE-clause. In MDX, the WHERE-clause should be used as a means for filtering the cube-space. Then everything in the SELECT-clause is based upon the remaining "sub-cube".

Also, there's no need for the calculated member...the SUM() function is redundant in this case (assuming the aggregation function specified for the [Measures].[Importe de la factura] is SUM).

Here's one way to write the query that is close to the format you have above...

SELECT
    {
        [Measures].[<<measure>>]
    } ON 0,
    {
        [Business Unit].[Division].[Division].Members
    } ON 1
FROM
    [<<cube>>]
WHERE
    {StrToMember("[Order Date].[Date Key].[" + Format(now(), "yyyyMMdd") + "]").Lag(59)
    :
    StrToMember("[Order Date].[Date Key].[" + Format(now(), "yyyyMMdd") + "]")}

Notice how the WHERE-clause specifies a set along the [Order Date] dimension. This basically says give me the entire cube space across all the dimensions...except for the order date dimension for which I just want the set specified".

Also, if you write the query like the following, it may be a bit easier to understand what's going on...

WITH
SET [Past 60 days] AS
    StrToMember("[Order Date].[Date Key].[" + Format(now(), "yyyyMMdd") + "]").Lag(59)
    :
    StrToMember("[Order Date].[Date Key].[" + Format(now(), "yyyyMMdd") + "]")
MEMBER [Measures].[Total Sales Past 60 days] AS
    SUM(
         [Past 60 days]
        ,[Measures].[<<measure>>]
    )
SELECT
    {
        [Measures].[Total Sales Past 60 days]
    } ON 0,
    {
        [Business Unit].[Division].[Division].Members
    } ON 1
FROM
    [<<cube>>]

Note: The "StrToMember()" formula works in my cube because the datekey is in YYYYMMDD format. yours may be in a different format so you may have to tweak it.