locked
Quantity * LAST known price RRS feed

  • Question

  • Hi, 

    I have 2 items in my pivot: quantity and price.

    I want to create a calculation in which whenever I have an order (Qty>0), a measure will calculate what is the last price * Qty.

    As an example: 

    [Total cost] in the year 2008 will be $98*1= $98

    [Total cost] in the year 2009 will be $98*2= $98

    [Total cost] in the year 2011 (if there were 4 orders) will be $115*4= $460

    Thx!


    עמית

    Thursday, February 19, 2015 9:08 PM

Answers

  • Hi and thanks for your comment...

    The solution was:

    =CALCULATE(MAX([Internal Time Stamp]), FILTER(Pricelist, Pricelist[Date]<= Product[Date]), FILTER(Pricelist, Product[Description] = Pricelist[Description]))

    Thx


    עמית

    • Marked as answer by עמיתת Sunday, February 22, 2015 12:08 PM
    Sunday, February 22, 2015 12:08 PM

All replies

  • Hi Amit,

    Why don't you use calculated column with the DAX formula =[price]*[quantity] ?


    Friday, February 20, 2015 11:16 AM
  • Hi Gil, 

    I am not sure that this is that simple. 

    I extract each of the items from different tables and the dates don't match.

    I assume the [price] measure you thought of is

    price:= sum(priceTBL[pricePERunit])

    When doing a multiplication of the 2 measures, you only get a result once you have both Qty & Price in the same date.

    So for instance, 2008 will yield a result, but 2009 won't (2 units * $0)



    עמית

    Friday, February 20, 2015 11:54 AM
  • Hey,

    Did you try the calculate function ?

    For example:

    LastPrice:=Calculate([pricefield],max(DimDate[datefield]))

    DImDate is dates table or the table where you maintain the prices dates

    Thank You,

    Aviad

    Sunday, February 22, 2015 7:46 AM
  • Hi and thanks for your comment...

    The solution was:

    =CALCULATE(MAX([Internal Time Stamp]), FILTER(Pricelist, Pricelist[Date]<= Product[Date]), FILTER(Pricelist, Product[Description] = Pricelist[Description]))

    Thx


    עמית

    • Marked as answer by עמיתת Sunday, February 22, 2015 12:08 PM
    Sunday, February 22, 2015 12:08 PM
  • Hi עמית,

    Thank you for your sharing which will help other forum members who have the similar issue.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, February 23, 2015 7:04 AM