locked
Difference in cost between most recent date and the second most recent date RRS feed

  • Question

  • Hello,

    Will appreciate any help on this.

    Have a table that list item#, date the standard cost went into effect and the standard cost.  How do I find the difference in StdCost on the last EffectiveDate and second to last EffectiveDate. 5.59 (01/05/2015) minus 5.81 (09/29/.014) = -.22.

    Thank you

    Item#      EffectiveDate    StdCost

    1152        01/01/2009      5.50

    1152        09/29/2014      5.81

    1152        04/04/2011      5.56

    1152        08/22/2012      5.45

    1152       10/27/2010       5.18

    1152       01/05/2015       5.59

    1152       07/12/2010       5.18

    1152       01/06/2014       5.84

    Wednesday, April 15, 2015 6:47 PM

Answers

  • The Last Costs can be calculated as 
    Last Costs:=CALCULATE(SUM([StdCost]),
    LASTNONBLANK('Table'[EffectiveDAte], SUM([StdCost]))
    )

    the previous last cost can be calculated as 

    Pre Last Costs:=CALCULATE([Last Costs],
    FILTER(ALL('Table'[EffectiveDate]),
        'Table'[EffectiveDate] < MAX('Table'[EffectiveDate]))
    )

    hth,
    gerhard


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

    • Proposed as answer by Michael Amadi Friday, April 17, 2015 7:34 PM
    • Marked as answer by MikeL03 Monday, April 20, 2015 7:45 PM
    Friday, April 17, 2015 1:36 PM
    Answerer

All replies

  • The Last Costs can be calculated as 
    Last Costs:=CALCULATE(SUM([StdCost]),
    LASTNONBLANK('Table'[EffectiveDAte], SUM([StdCost]))
    )

    the previous last cost can be calculated as 

    Pre Last Costs:=CALCULATE([Last Costs],
    FILTER(ALL('Table'[EffectiveDate]),
        'Table'[EffectiveDate] < MAX('Table'[EffectiveDate]))
    )

    hth,
    gerhard


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

    • Proposed as answer by Michael Amadi Friday, April 17, 2015 7:34 PM
    • Marked as answer by MikeL03 Monday, April 20, 2015 7:45 PM
    Friday, April 17, 2015 1:36 PM
    Answerer
  • Gerhard,

    Perfect - thank you.

    Have read a book, did the Power Pro videos but some of you people make it look so easy - maybe someday.

    Thanks again,

    Mike

    Monday, April 20, 2015 7:49 PM