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.
Hi,
I've been trying to find a way to add a new column not a measure that shows the closing price of a stock from last trading day. It has to be from the last trading day not from the day before because of weekends and holidays when the stock market is closed.
The bolded column below is the column that I need. I have another column for the name of the stock, I don't know if that helps.
Trading date | Closing price | Yesterday closing price |
1.3.2021 | 14.013 | |
2.3.2021 | 14.040 | 14.013 |
3.3.2021 | 14.080 | 14.040 |
4.3.2021 | 14.056 | 14.080 |
5.3.2021 | 13.921 | 14.056 |
8.3.2021 | 14.381 | 13.921 |
9.3.2021 | 14.438 | 14.381 |
Thanks,
Robert
Hi @Rolosig,
Did above suggestions help with your scenario? if that is the case, you can consider Kudo or accept that suggestions to help others who faced similar requirements to find it more quickly.
If these also not help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi, @Rolosig
Please try the below for the Calculated Column.
Yesterday Closing Price Column =
VAR lastdateinthelist =
CALCULATE (
MAX ( 'Table'[Trading date] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Stock Name] ),
'Table'[Trading date] < EARLIER ( 'Table'[Trading date] )
)
)
RETURN
CALCULATE (
SUM ( 'Table'[ Closing price ] ),
FILTER ( 'Table', 'Table'[Trading date] = lastdateinthelist )
)
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim , I don't know why but I'm getting the yesterday's closing price. Also I'm getting 0 when there is a holiday the day before, which I don't want. I want to be able to get the price from the last day when the stock market is open.
I hope this picture helps.
All the best,
Robert
Hi,
I think your first question was about trying to create a column.
Do you want to create a measure?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim ,
My end goal was to find the standard deviation of daily returns of a stock. I was able to do a measure for daily return but I wasn't able to find the standard deviation so I wanted to try to do it with a column.
Hi, @Rolosig
Please check the below picture.
I used the same Calculated Column Formula to create the below.
I think it works fine.
Please correct me if I missed something.
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.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim Yes I did the same thing as you but it doesn't seem to filter down to one Stock name. It looks like it is giving me the sum of all yesterday closing prices, since I have more than one Stock name.
@Rolosig , a New column like
Yesterday closing price =
var _max = maxx(filter(Table, [Date] < earlier([Date])),[Date])
return
maxx(filter(Table, [Date] =_max ),[Closing price])
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |