Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
can you please help me to find a custom formula or quick measure to calculate these daily retun of stocks price?
table:
ID | OBS_DATE MM/DD/YYYY | PRICE | WANTED COLUM - RETURN daily % change |
100018387 | 3/2/2020 | 64.77 | ----- |
100018387 | 3/3/2020 | 63.78 | -1.53% |
100018387 | 3/4/2020 | 68.05 | 6.69% |
100030418 | 3/2/2020 | 38.27 | ----- |
100030418 | 3/3/2020 | 37.23 | -2.72% |
100030418 | 3/4/2020 | 38.75 | 4.08% |
Thank you!!
Solved! Go to Solution.
Hi @Anonymous ,
You can create one measure as below:
Daily % change = var curdate=MAX('Daily Return'[OBS_DATE])
var predate=CALCULATE(MAX('Daily Return'[OBS_DATE]),'Daily Return'[OBS_DATE]<curdate)
var priceinPredate=CALCULATE(MAX('Daily Return'[PRICE]),'Daily Return'[OBS_DATE]=predate)
return IF(NOT(ISBLANK(priceinPredate)),DIVIDE(MAX('Daily Return'[PRICE])-priceinPredate,MAX('Daily Return'[PRICE])))
Best Regards
Rena
Hi @Anonymous ,
You can create one measure as below:
Daily % change = var curdate=MAX('Daily Return'[OBS_DATE])
var predate=CALCULATE(MAX('Daily Return'[OBS_DATE]),'Daily Return'[OBS_DATE]<curdate)
var priceinPredate=CALCULATE(MAX('Daily Return'[PRICE]),'Daily Return'[OBS_DATE]=predate)
return IF(NOT(ISBLANK(priceinPredate)),DIVIDE(MAX('Daily Return'[PRICE])-priceinPredate,MAX('Daily Return'[PRICE])))
Best Regards
Rena
Hi @v-yiruan-msft Rena,
Thank you for the support! This is exactly what I was looking for.
If possible, could you please tell how would I add this daily % change as a custom colum instead of a measure? Will this have any impact on performaces?
Much appreciate, regards,
Luca.
Hi @Anonymous ,
The biggest difference between calculated column and measure is that calculated column is stored in memory, consuming a certain amount of memory. It will affect the performance of query calculations when the amount of data is large. You can find the details in this post.
You can also create one calculated column as below base on your scenario:
Prevprice = var a ='Daily Return'[OBS_DATE]
var b= CALCULATE(MAX('Daily Return'[OBS_DATE]),FILTER('Daily Return','Daily Return'[ID]=EARLIER('Daily Return'[ID])&&'Daily Return'[OBS_DATE]<a))
var c= CALCULATE(max('Daily Return'[PRICE]),FILTER('Daily Return','Daily Return'[ID]=EARLIER('Daily Return'[ID])&&'Daily Return'[OBS_DATE]=b))
return IF(NOT(ISBLANK(c)),DIVIDE('Daily Return'[PRICE]-c,'Daily Return'[PRICE]))
Best Regards
Rena
@Anonymous ,
You can try this measure:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |