locked
Merging 2 queries to create a Quantity * LAST KNOWN price calculation RRS feed

  • Question

  • Hi, 

    I have an interesting problem, which I thought of overcoming it in Power Pivot, and realized it is a bit trickier than I thought + it will be cumbersome when my database will grow.

    Therefore, I thought that by using the M language, I can bypass the problem and even create a better database.

    I have 2 folders: 

    1) Price- contains multiple XLS files, with prices per unit for specific items. The date of update is in the file name.  

    2) Qty- contains multiple XLS files, with quantities of specific items. The order date <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="a026df9f-2e97-47dd-80ba-44f9e9669b33" id="e613bf05-4370-4d09-95b6-c4bf5b5ecd78">is in an excel</gs> column. 

    I want to create PRICE * QUANTITY calculation to know how much I paid when I ordered a product.

    To do so, I need to know what was the last known price.

    As an example:

    In year 2008 I paid $691*1173 products. 

    In year 2009 I paid $691 (last known price) * 1173 products.

    In year 2010 the price updated and therefore in 2011 I will pay $1729 * 500  products.

    An example can be found here:

    http://1drv.ms/1DGXwLa

    THX!


    עמית


    • Edited by עמיתת Friday, February 20, 2015 6:31 PM
    Friday, February 20, 2015 6:26 PM

Answers

  • you can use this formula to calculate the last known price

    Last Valid Price:=CALCULATE(
    SUM(Price[Price]),
    LASTNONBLANK(DATESBETWEEN('DatesTbl'[Dates], BLANK(), MAX('DatesTbl'[Dates])), CALCULATE(SUM(Price[Price]))))

    you can then simply multiply it with your quantities and should get the desired result

    hth,
    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Michael Amadi Wednesday, February 25, 2015 11:54 AM
    • Marked as answer by Charlie Liao Monday, March 2, 2015 2:44 PM
    Wednesday, February 25, 2015 7:47 AM
    Answerer

All replies

  • Hi, 

    I have an interesting problem, which I thought of overcoming it in Power Pivot, and realized it is a bit trickier than I thought + it will be cumbersome when my database will grow.

    Therefore, I thought that by using the M language, I can bypass the problem and even create a better database.

    I have 2 folders: 

    1) Price- contains multiple XLS files, with prices per unit for specific items. The date of update is in the file name.  

    2) Qty- contains multiple XLS files, with quantities of specific items. The order date in an excel column. 

    I want to create PRICE * QUANTITY calculation to know how much I paid when I ordered a product.

    To do so, I need to know what was the last known price.

    As an example:

    In year 2008 I paid $691*1173 products. 

    In year 2009 I paid $691 (last known price) * 1173 products.

    In year 2010 the price updated and therefore in 2011 I will pay $1729 * 500  products.

    An example can be found here:

    http://1drv.ms/1DGXwLa

    THX!


    עמית

    Friday, February 20, 2015 6:32 PM
  • you can use this formula to calculate the last known price

    Last Valid Price:=CALCULATE(
    SUM(Price[Price]),
    LASTNONBLANK(DATESBETWEEN('DatesTbl'[Dates], BLANK(), MAX('DatesTbl'[Dates])), CALCULATE(SUM(Price[Price]))))

    you can then simply multiply it with your quantities and should get the desired result

    hth,
    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Michael Amadi Wednesday, February 25, 2015 11:54 AM
    • Marked as answer by Charlie Liao Monday, March 2, 2015 2:44 PM
    Wednesday, February 25, 2015 7:47 AM
    Answerer
  • Thank You

    עמית

    Sunday, March 22, 2015 8:15 AM