Calculating Price change and % Growth

[KM007]

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

[Greg_Deckler]

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.

[KM007]

Thank 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)

[KM007]

@Greg_Deckler thank you so much, works great!

How may I create columns for

• % Change (which is column just added divided by previous day value)
• YTD % Change (current value divided by value with Index value = 1)

Thanks for helping me

[Greg_Deckler]

OK, it may be easier to do it this way:

Create a column to just get the previous value like this:

Previous Value = MAXX(FILTER(Funds,Funds[Index]=EARLIER(Funds[Index])-1),Funds[Value])

Now that you have that, create another column like this:

Change = [Value] - [Previous Value]

Then, you can create another column:

% Change = [Change] / [Previous Value]

And then finally, another column:

YTD % Change = [Value]/CALCULATE(SUM([Value]),FILTER(Funds,[Index]=1))

[KM007]

Hi @Greg_Deckler

May I ask for your assistance further

Please see attached photo.

Column "Previous Value" is showing an incorrect value and I don't fully comprehend the formula to decipher where error is.

Thanks

[Greg_Deckler · Accepted solution]

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.

[fleal]

this formula worked perfectly in my database, but the performance was pretty bad. Could someone help me get the same result using DAX (not calculated column)?

[KM007]

That worked! Yay

Thank you

[v-huizhn-msft]

Hi @KM007,

As you have resolved your issue, please mark the right reply as answer, so more people like you can get solution easily and clearly. Thanks for inderstanding.

Best Regards,
Angelia