Calculating Difference between sequential values in PowerPivot

[Blemish66]

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

 

[ImkeF]

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

 

[Blemish66]

Hi Imke

YOU ARE BRILLIANT !

your first suggestion worked a treat thank you .... however I may not be so brilliant as I perhaps left out one use case

Where in the one second interval there may have been no trade i.e. over 2 (or more) consecutive seconds the result of your function produces the same value .... I need to figure out a way to then compare the 2 results and if they are the same then the latter should become zero.

Not sure what Power Query brings to the table tbh, but I am in Australia and at this point Power Query is not available through the 365 platform in this region

Sorry quite new to this, but thanks again for your help.


...

Blemish

 

[ImkeF]

Thanks for the flowers
Similar technique: Finding Duplicate Rows in PowerPivot–Across Tables or Within Tables « PowerPivotPro

Enjoy, Imke