Forum Discussion
Helper IICalculating Price change and % Growth
Hi
I have a table with 596 Fund Names and contains prices(Value).
I wish to add 3 x columns:
- Price change
- % change
- YTD % change
Many thanks
OK, the formula is:
Previous Value = MAXX(FILTER(Funds,Funds[Index]=(EARLIER(Funds[Index])-1)),Funds[Value])
MAXX
The MAXX is just any table aggregation. We could also have used MINX or SUMX here because we only intend to return a single row. MAXX's formula generically is MAXX(Table,Expression). In this case, the results of our FILTER clause are out table and we are returning the MAX of the Value column from the single row returned by the FILTER clause.
FILTER
We are filtering the Funds table where a row's [Index] equals the CURRENT (EARLIER) value of the [Index] minus 1. So, what is going on here is that if a row has an Index of 7, EARLIER will take on that value, I added parenthesis around the EARLIER and -1 to make certain this is happening correctly. So, when we FILTER the table, we will return the row whose Index is 7-1 or 6.
So, what may be going on here is what I was afraid of originally, you Index field is not in order by Date. So, you will probably have to go back to:
Previous Value = VAR previousDate = MAXX( FILTER(Funds, Funds[Fund Name]=EARLIER(Funds[Fund Name]) && Funds[Date]<EARLIER(Funds[Date])),Funds[Date]) RETURN MAXX(FILTER(Funds,Funds[Fund Name]=EARLIER(Funds[Fund Name]) && Funds[Date] = previousDate),Funds[Value])
This *should* do the same thing but ensure that you are only dealing with the same fund and the order of the Index doesn't matter, it will always return the previous date's Value.
10 Replies
- Greg_Deckler
Community Champion
You are going to want some variation of this:
Column =
VAR previousDate = MAXX(
FILTER(Funds,
Funds[Fund Name]=EARLIER(Funds[Fund Name]) && Funds[Date]<EARLIER(Funds[Date])),Funds[Date])
VAR previousValue = MAXX(FILTER(Funds,Funds[Fund Name]=EARLIER(Funds[Fund Name]) && Funds[Date] = previousDate),Funds[Value])
RETURN IF(previousDate = BLANK(),Funds[Value],Funds[Value]-previousValue)The above assumes that you can't just use Index-1 to get the previous row as I assume all of these funds are jumbled up in the data. The rest should be fairly straight-forward.
KM007Thank you Greg_Deckler for you help!
I would like to use Index as there are some days missing.
What would the calculation be using Index
Much appreciated
- Greg_Deckler
Just use:
Column = VAR previousValue = MAXX(FILTER(Funds,Funds[Index]=EARLIER(Funds[Index])-1),Funds[Value]) RETURN IF(previousValue = BLANK(),Funds[Value],Funds[Value]-previousValue)