locked
Calculating Difference between sequential values RRS feed

  • Question

  • Hi,

    I have a stock price related dataset that is generated every second (including stock_name, timestamp, last_price matched, total_volume traded). The total volume traded is a running total from a point in time. The dataset has multiple different stocks.

    I am trying to calculate the volume traded in each second for each stock. i.e. calculate the difference between sequential total_volume_traded figures for each stock. I “think” I need to do this as a calculated column as I need the result in a further calculation. Ultimately I am looking to calculate a weighted price over time.

    I have tried to create a filtered set for just the 2 sequential rows. This doesn’t produce an error it just displays no value:

    =CALCULATE(

    SUMX(FILTER(Stocks,Stocks[Seconds_from_midnight+1]=Stocks[Seconds from midnight]+1),Stocks[Stock_total_matched]),

    ALLEXCEPT(

    Stocks,

    Stocks[stock_name],

    Stocks[Seconds_from_midnight+1]

    )

    )

    Any suggestions as to how I might tackle this would be much appreciated and gratefully received.

    Thanks

    Monday, February 16, 2015 2:05 AM

Answers

  • Hi Blemish,

    this should give you the column with the transaction volume for your weighted average calculation:

    Transaction:=[total_volume traded]-

    CALCULATE(MAX([total_volume_traded]);

    FILTER('Stocks';Stocks[timestamp]<EARLIER([timestamp])

    &&[stock_name]=EARLIER([stock_name])))

    It basically subtracts the total amount of the previous date from your current total (on all same stock_names).

    Provided that you don't have negative amounts of "total_volume traded" (which I assumed).

    However, as long as your dataset goes back until the beginning of the range where total_volume traded begins, its fine. But if you start somewhere in between, the first value will not only give the transaction of the second, but the total. If you want to supress that value, you need to wrap this formular in this:

    =IF(CALCULATE(MIN([timestamp]);FILTER('Stocks';[stock_name]=EARLIER([stock_name])))<>[timestamp];Transaction;0)

    Wonder whether there is a shorter solution for it.

    If you're experiencing performance issues here, try Power Query, I can post the code if you like.


    hth, Imke


    Tuesday, February 17, 2015 12:24 PM
    Answerer