Forum Discussion
Calculate daily return rate
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 |
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.
4 Replies
- Jihwan_Kim
Super User
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.
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?
- Jihwan_Kim
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.
