Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have an issue, where I am trying to calculate the average market value during the holding period (which will be reset on year-end). The issue is that I have data as follows:
As Of Date | ISIN | Market Value | Avg. MV (currently takes the whole year so Jan = 0, Feb = 0, Nov = 0 and Dec = 0) | Avg. Market Value (what I want) |
31-03-2022 | BlaBlaBlaBla1 | 100 (start of trade) | 33,33 | 100 |
30-04-2022 | BlaBlaBlaBla1 | 110 | 52,5 | 105 |
31-05-2022 | BlaBlaBlaBla1 | 110 | 64 | 106,66 |
30-06-2022 | BlaBlaBlaBla1 | 112 | 72 | 108 |
31-07-2022 | BlaBlaBlaBla1 | 105 | 76,71 | 107,4 |
31-08-2022 | BlaBlaBlaBla1 | 107 | 80,50 | 107,33 |
30-09-2022 | BlaBlaBlaBla1 | 108 | 83,56 | 107,42 |
31-10-2022 | BlaBlaBlaBla1 | 110 | 86,20 | 107,75 |
30-11-2022 | BlaBlaBlaBla1 | SOLD HERE so Avg MV should repeat when MV is 0 | 78,36 | 107,75 |
31-12-2022 | BlaBlaBlaBla1 | SOLD HERE so Avg MV should repeat when MV is 0 | 71,83 | 107,75 |
This is just one sample ISIN code, but there are 1000's of ISINs and one ISIN can be present in multiple portfolios in my datasource. How would I be able to repeat the Avg. Market Value at the time it was sold? My current data generates the it based on all months during the year just placing 0 in the months in which it was not present in the portfolio, which is leading to fairly low average market values.
My toughts was generating a flagging system, to flag wheter the trade is active or not.
Let me know if anything else is required - I really appreciate your help.
Best Regards,
Solle
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @Ashish_Mathur,
Thank you for your reply and the attached file, this is exactly what I needed.
Thank you and have a nice day.
Best Regards,
You are welcome.
hi @lbendlin,
Thank you for your reply it helped me 🙂
Have a nice day 🙂
Best Regards,
Jeppe Solgaard
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |