# MDX - Calculated measure over date dimension • ### Question

• I have a fact table made up of products and the effective dates they became active with a measure "Charge".  I need a calculated measure that reports the Variance in the "Charge" based on the earliest "Effective Date" (Min)? and the latest "Effective Date" (Max)?  Is this possible and if so could someone off some help?  Thanks in advance.
Saturday, November 27, 2010 1:46 PM

• A couple of points:

- Each cube MDX script statement should be terminated by a semi-colon

- The [Date] level should be explicitly specified, to exclude the [All] level member

Something like this:

Create
Member CurrentCube.[Measures].[ProductBeginDate] as
NonEmpty
([Dim Charge Master Date].[Effective Date].[Effective Date].Members,
[Measures].[Chrg]).item(0).MemberValue;

Create
Member CurrentCube.[Measures].[ProductEndDate] as
Tail
(NonEmpty([Dim Charge Master Date].[Effective Date].[Effective Date].Members,
[Measures].[Chrg])).item(0).MemberValue;

Create
Member CurrentCube.[Measures].[Price Variance] as
([Measures].[Average Unit Price],
Tail
(NonEmpty([Dim Charge Master Date].[Effective Date].[Effective Date].Members,
[Measures].[Chrg])).item(0))
- ([Measures].[Average Unit Price],
NonEmpty
([Dim Charge Master Date].[Effective Date].[Effective Date].Members,
[Measures].[Chrg]).item(0)),
FORMAT_STRING
= "Currency";

- Deepak
Monday, November 29, 2010 8:21 PM

### All replies

• Could you explain what the sample for the Variance is, with an example - is each day considered a single sample? Here's an Adventure Works query that computes the Variance of [Average Unit Price] over daily samples - Variance being the square of Standard Deviation:

With
Member [Measures].[Variance Unit Price] as
StdDev(existing [Date].[Date].[Date].Members,
[Measures].[Average Unit Price]) ^ 2
select
{[Measures].[Average Unit Price],
[Measures].[Variance Unit Price]} on 0,
[Product].[Category].Members on 1
---------------------------------------------
Average Unit Price Variance Unit Price
Accessories \$19.69 4.94649452697312
Bikes \$1,255.08 640865.291277584
Clothing \$32.09 35.6922343166121
Components \$251.40 7477.69733742728

- Deepak
Sunday, November 28, 2010 5:15 PM
• Thanks so much for your help with this, I am very new to using MDX and creating calculated measures in Analysis Services Cubes.

I am just trying to get a calculated measure that reports the difference between prices on the same item for different dates.

Product ID   EffDate    EffDate

7/24/2010      11/1/2010

556677     10.00               15.00           5.00        50%

568992      2.60                 6.00            3.40      130.7%

The cube is a price/product list loaded at different dates (effective date).  I want to report the difference between prices between the earliest date the list was loaded and the latest date (most recent) the list was loaded.  I am using Excel and have been calculating the difference outside of the pivot table but reading through forumns like this it seemed like this could happen as a calculated measure.  Again - thank you very much.

Sunday, November 28, 2010 5:30 PM
• Here's another Adventure WOrks sample query, which returns the price variance between earliest and latest sale dates for various bikes:

With
Member [Measures].[ProductBeginDate] as
NonEmpty([Date].[Date].[Date].Members,
[Measures].[Unit Price]).item(0).MemberValue
Member [Measures].[ProductEndDate] as
Tail(NonEmpty([Date].[Date].[Date].Members,
[Measures].[Unit Price])).item(0).MemberValue
Member [Measures].[Price Variance] as
([Measures].[Average Unit Price],
Tail(NonEmpty([Date].[Date].[Date].Members,
[Measures].[Unit Price])).item(0))
- ([Measures].[Average Unit Price],
NonEmpty([Date].[Date].[Date].Members,
[Measures].[Unit Price]).item(0)),
FORMAT_STRING = "Currency"
select
{[Measures].[ProductBeginDate],
[Measures].[ProductEndDate],
[Measures].[Average Unit Price],
[Measures].[Price Variance]} on 0,
[Product].[Product].[Product].Members on 1
where [Product].[Category].[Bikes]
--------------------------------------------
ProductBeginDate ProductEndDate Average Unit Price Price Variance
Mountain-100 Black, 38 7/1/2005 6/29/2006 \$2,221.00 \$1,350.00
Mountain-100 Black, 42 7/1/2005 6/29/2006 \$2,197.89 \$1,350.00
...

- Deepak
Sunday, November 28, 2010 6:35 PM
• Deepak

Thank you and please have patience with me because I am new at this.  I have tried to modify your example to work in my cube as shown below but I get an error during build and deploy saying "... the syntax for With is incorrect"

Can you assist me further to get this working?  Thanking you in advance.

With

Member

[Measures].[ProductBeginDate] as

NonEmpty

([Dim Charge Master Date].[Effective Date].Members,

[Measures].[Chrg]).

item(0).MemberValue

Member

[Measures].[ProductEndDate] as

Tail

(NonEmpty([Dim Charge Master Date].[Effective Date].Members,

[Measures].[Chrg)).item(0).MemberValue

Member

[Measures].[Price Variance] as[Measures].[Chrg],

Tail

(NonEmpty([Dim Charge Master Date].[Effective Date].Members,

[Measures].[Chrg])).

item(0))

- ([Measures].[Chrg],

NonEmpty

([Dim Charge Master Date].[Effective Date].Members,

[Measures].[Chrg]).

item(0)),

FORMAT_STRING

= "Currency"

Monday, November 29, 2010 6:09 PM
• The "with" syntax creates query-scoped calculated members, which only work within an MDX query - like the Adventure Works sample. To create these members in the cube MDX script, you can use the "create" syntax, like:

CREATE MEMBER CURRENTCUBE.[Measures].[ProductBeginDate] as

([Dim Charge Master Date].[Effective Date].Members, item(0).MemberValue;

This BOL entry explains it in more detail:

# CREATE MEMBER Statement (MDX)

- Deepak

[Measures].[Chrg]).

NonEmpty

Monday, November 29, 2010 7:23 PM
• Well, I thank you again!  However I am not getting it to work.  As I said initially, I am trying to come up with the MDX that will populate the exprression pane in the "New Calculated Member" of a cube.  Maybe what I want to do will not work but most probably I am just not smart enough to get it working having never studied this MDX.

Now I have The syntax for "Create" is incorrect.

Create

Member CurrentCube.[Measures].[ProductBeginDate] as

NonEmpty

([Dim Charge Master Date].[Effective Date].Members,

[Measures].[Chrg]).

item(0).MemberValue

Create

Member CurrentCube.[Measures].[ProductEndDate] as

Tail

(NonEmpty([Dim Charge Master Date].[Effective Date].Members,

[Measures].[Chrg])).

item(0).MemberValue

Create

Member CurrentCube.[Measures].[Price Variance] as

([Measures].[Average Unit Price],

Tail

(NonEmpty([Dim Charge Master Date].[Effective Date].Members,

[Measures].[Chrg])).

item(0))

- ([Measures].[Average Unit Price],

NonEmpty

([Dim Charge Master Date].[Effective Date].Members,

[Measures].[Chrg]).

item(0)),

FORMAT_STRING

= "Currency"

Monday, November 29, 2010 8:12 PM
• A couple of points:

- Each cube MDX script statement should be terminated by a semi-colon

- The [Date] level should be explicitly specified, to exclude the [All] level member

Something like this:

Create
Member CurrentCube.[Measures].[ProductBeginDate] as
NonEmpty
([Dim Charge Master Date].[Effective Date].[Effective Date].Members,
[Measures].[Chrg]).item(0).MemberValue;

Create
Member CurrentCube.[Measures].[ProductEndDate] as
Tail
(NonEmpty([Dim Charge Master Date].[Effective Date].[Effective Date].Members,
[Measures].[Chrg])).item(0).MemberValue;

Create
Member CurrentCube.[Measures].[Price Variance] as
([Measures].[Average Unit Price],
Tail
(NonEmpty([Dim Charge Master Date].[Effective Date].[Effective Date].Members,
[Measures].[Chrg])).item(0))
- ([Measures].[Average Unit Price],
NonEmpty
([Dim Charge Master Date].[Effective Date].[Effective Date].Members,
[Measures].[Chrg]).item(0)),
FORMAT_STRING
= "Currency";

- Deepak
Monday, November 29, 2010 8:21 PM