Answered by:
Quantity * LAST known price
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] ?
 Edited by Gil RavivMVP Friday, February 20, 2015 11:16 AM
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 