MDX last order date and last order value

Question

I've googled but I cannot get the point

I've a fact table like this one

fact_order

id, id_date, amount id_supplier 1 1 100 4 2 3 200 4

where id_date is the primary key for a dimension that have

id date month 1 01/01/2011 january 2 02/01/2011 january 3

I would like to write a calculated member that give me the last date and the last amount for the same supplier.

Max Max · Accepted Answer · 2011-08-20T22:29:19

Last date and last amount -- it's a maximum values for this supplier?

If "yes", so you can create two metrics with aggregation "max" for fields id_date and amount. And convert max id_date to appropriate view in the following way:

CREATE MEMBER CURRENTCUBE.[Measures].[Max Date]
 AS 
IIF([Measures].[Max Date Key] is NULL,NULL,
STRTOMEMBER("[Date].[Calendar].[Date].&["+STR([Measures].[Max Date Key])+"]").name), 
VISIBLE = 1  ;

It will works, If maximum dates in your dictionary have maximum IDs. In my opinion You should use date_id not 1,2,3..., but 20110101, 20110102, etc.

If you don't want to obtain max values - please provide more details and small example.

MDX last order date and last order value

1 Answers