Calculate daily return rate

[stella1215]

hello everyone!

I want to add a calculated column of the daily return rate by the company. My data looks like below. I want to use today's price divided by yesterday's price, and so on. For example, 2021.4.7's price / 2021.4.6's price. How can I achieve this?

Thank you!

Date	company	Price
2021.4.6	600001	8.51
2021.4.7	600001	9.41
2021.4.8	600001	9.1
2021.4.6	600002	9.88
2021.4.7	600002	10.78
2021.4.8	600002	10.52
2021.4.6	600003	10.62
2021.4.7	600003	10.32
2021.4.8	600003	10.43

[Jihwan_Kim]

Hi, @stella1215 

Please try the below calculated column.

Daily Return Rate Column =
DIVIDE (
Data[Price],
VAR previousdate =
CALCULATE (
MAX ( Data[Date] ),
FILTER (
ALLEXCEPT ( Data, Data[company] ),
Data[Date] < EARLIER ( Data[Date] )
)
)
RETURN
CALCULATE (
SUM ( Data[Price] ),
FILTER ( ALLEXCEPT ( Data, Data[company] ), Data[Date] = previousdate )
),
BLANK ()
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[stella1215]

Thank you for your help! I have another question: is it possible to achieve this in a measure? I applied your DAX to get that calculated column. Since my dataset is kinda big, it slowed down the entire report compared to when I directly import the return rate. I was wondering if making it a measure can do the same trick and speed the loading process?

[Anonymous]

Hi @stella1215 

You should not have it as a measure. You should use Power Query to calculate this. The DAX given by @Jihwan_Kim for the calculation of the column is slow because one should never use CALCULATE when creating columns in DAX. CALCULATE executes context transition on every row in the table and this is a very expensive operation. Your column can be calculated in DAX without CALCULATE but I would do it in Power Query since this is where the calculation truly belongs and it's the best solution for many reasons.

[Jihwan_Kim · Accepted solution]

Hi, @stella1215 

Please check the below picture and the sample pbix file's link down below.

Daily Return Rate Measure =
VAR currentdate =
MAX ( Data[Date] )
VAR currentcompany =
MAX ( Data[company] )
VAR previousdate =
CALCULATE (
MAX ( Data[Date] ),
FILTER ( ALLEXCEPT ( Data, Data[company] ), Data[Date] < currentdate )
)
VAR previousprice =
CALCULATE ( [Today Price], Data[Date] = previousdate )
RETURN
DIVIDE ( [Today Price], previousprice, BLANK () )

https://www.dropbox.com/s/oi2fqd0cn62e0gq/stella1215.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.