none
MDX - Calculated measure over date dimension RRS feed

  • 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

Answers

  • 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
    Moderator

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
    from [Adventure Works]
    ---------------------------------------------
     Average Unit Price Variance Unit Price
    All Products $465.18 1517706.95146314
    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
    Moderator
  • 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
    from [Adventure Works]
    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
    Moderator
  • 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
    Moderator
  • 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
    Moderator