none
Get latest date price (Get value based on max of date dimension) RRS feed

  • Question

  • Hi,

    I have a measure group product price, the measures are

    1. Product Id

    2. Price

    3. Date

    I have mapped the product id and date with product and date dimensions.

    I need to get the latest price of the product.

    For example:

    Prod1, 100, 1/1/2015

    Prod1, 300, 2/1/2015

    Prod1, 250, 3/1/2015

    Prod1, 150, 10/1/2015

    So, i need the latest price. The latest price is 150.

    I new to MDX query. Could you please explain me the MDX query to achieve this?

    Thanks,

    Hari

    Wednesday, November 18, 2015 8:37 AM

Answers

  • I would tackle this slightly differently with something like the following:

    WITH MEMBER measures.LastPrice as (TAIL(
      NONEMPTY( Existing [Date].[Date].[Date].members , [Measures].[Price])
    ).Item(0).item(0) , [Measures].[Price])

    The TAIL() function gets the last member of a set. If you've configured your date dimension properly your dates should be in order so the latest date is at the end.

    The NONEMPTY function limits the set to only those dates that have a value for [Price]

    The EXISTING keyword will filter the [Date] members so that only those that are part of the current filter context are considered. So if you have Year on the filter, or months on the columns or any other date attribute in play that that will be taken into consideration.

    The .Item(0).Item(0) returns the first member from the first tuple from the set, technically TAIL can return a set of one or more tuples, so this gets the last date member out of that set.

    Then finally I am constructing a tuple - a comma separated list of members surrounded by brackets. Which is like a coordinate into the cube to make sure that it is the value for the [Price] measure that is returned for the last date.

    You'll notice that I have not referenced [Product] at all in this calculation. That is because I want to pick-up the current product from the current context. I'm assuming that you'd want to use this measure when you have products on the rows/columns or filter.

    If you wanted you could wrap this in some sort of IF condition so that it only works when a product is in context.


    http://darren.gosbell.com - please mark correct answers

    Wednesday, November 18, 2015 10:06 AM
    Moderator

All replies

  • You can simply create a calculation member for this: max(measure). So you'll have this kind of information every time you need.

    Please mark as answer if this post helped you


    • Edited by DIEGOCTN Wednesday, November 18, 2015 9:11 AM
    Wednesday, November 18, 2015 9:10 AM
  • Hi,

    You can write something like below

    WITH MEMBER Measures.x AS Max 
       ([Order Date].[Calendar Year].[CalendarYear]
          , [Measures].[Reseller Order Quantity]
       )
    SELECT Measures.x ON 0,
    [DimProduct].[Product By Category].[ProductCategory].members
    on rows
    from [ResellerSales];


    Cheers,

    Amit Tomar

    ---------------------------------------------------

    Please mark this as answer if it solved your query

    Please vote this as helpful if it solved your query

    ---------------------------------------------------

    My Blog My Wiki Page

    Wednesday, November 18, 2015 9:30 AM
  • I would tackle this slightly differently with something like the following:

    WITH MEMBER measures.LastPrice as (TAIL(
      NONEMPTY( Existing [Date].[Date].[Date].members , [Measures].[Price])
    ).Item(0).item(0) , [Measures].[Price])

    The TAIL() function gets the last member of a set. If you've configured your date dimension properly your dates should be in order so the latest date is at the end.

    The NONEMPTY function limits the set to only those dates that have a value for [Price]

    The EXISTING keyword will filter the [Date] members so that only those that are part of the current filter context are considered. So if you have Year on the filter, or months on the columns or any other date attribute in play that that will be taken into consideration.

    The .Item(0).Item(0) returns the first member from the first tuple from the set, technically TAIL can return a set of one or more tuples, so this gets the last date member out of that set.

    Then finally I am constructing a tuple - a comma separated list of members surrounded by brackets. Which is like a coordinate into the cube to make sure that it is the value for the [Price] measure that is returned for the last date.

    You'll notice that I have not referenced [Product] at all in this calculation. That is because I want to pick-up the current product from the current context. I'm assuming that you'd want to use this measure when you have products on the rows/columns or filter.

    If you wanted you could wrap this in some sort of IF condition so that it only works when a product is in context.


    http://darren.gosbell.com - please mark correct answers

    Wednesday, November 18, 2015 10:06 AM
    Moderator
  • Dear Darren Gosbell,

    you post is very helpful for me. i have use below code for last sold price and got correct results.


    CREATE MEMBER CURRENTCUBE.[Measures].[Last Sold Price]
     AS 
    case when [Product].[Item ID].level.name <>"(All)"  then

    (IIF([Measures].[Qty - Closing Stock]<>0,
        (tail (
                NonEmpty(
                        [Time].[Date].members
                        * [Measures].[Price - Item Price]
                         )
              ).Item(0)
         )
    ,0)
    )  * [Measures].[Qty - Closing Stock]
    end
    , VISIBLE = 1  ; 

    but problem is in Item's parents totals and Grand Total both are missing i need sum of Item's total and Grand total values as shown in below picture.


    Abid Ali

    Friday, July 26, 2019 6:35 AM
  • but problem is in Item's parents totals and Grand Total both are missing i need sum of Item's total and Grand total values as shown in below picture.

    Thats because the Grand Total is calculated at the (All) level and you've explicitly blocked that from returning anything with your outer case statement.

    http://darren.gosbell.com - please mark correct answers

    Friday, July 26, 2019 2:57 PM
    Moderator