Find closing price in stock exchange transactions

[hlombard]

I have a (powerpivot) table with the following fields from transactions on a stock exchange:

1. Sequence number
2. DateTime of transaction
3. Price at which the trade occured

I need to add a Calculated Column (not a Measure) that shows the closing price for each day (on every row).

So for each day it should return the Price of the last trade on that day.

Your help is appreciated!

 

[Jacob Barnett]

Try this:

- Create an additional column which is just the date (no time) called Date. You could use ROUNDDOWN(Datetime,0).

- Write the measure that returns a table made up of the top/highest DateTime to find the corresponding value: =CALCULATE(MAX([Price]),TOPN(1, FILTER(Table1,[Date]=EARLIER([Date])),1))

Hope this helps.

 

[hlombard]

Thanks for your quick response!

I tried it and it almost works.

Instead of returning the last price on the day it returns the highest price for the day.
I think it is the Max(price) that causes that.

How can I return the price of the Max(Sequence no) ?
The highest sequence number on the day is also the last price of the day.

 

[Jacob Barnett]

Sorry, messed up the final argument of the TOPN! Try this:

=CALCULATE(MAX([Price]),TOPN(1, FILTER(Table1, table1[Date]=EARLIER(table1[Date])),Table1[Sequence]))

 

[hlombard]

Thanks - that worked!