Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KM007
Helper II
Helper II

Calculating 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

FundPerformance - Query Editor.png

1 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

10 REPLIES 10
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

 

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Screenshot.png

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

That worked! Yay

 

Thank you

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.