Average Market Value of Instrument in PowerBI

[Solle]

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

[lbendlin · Accepted solution]

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Solle]

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,

[Ashish_Mathur]

You are welcome.

[lbendlin · Accepted solution]

[Solle]

hi @lbendlin,
Thank you for your reply it helped me 🙂
Have a nice day 🙂 

Best Regards,
Jeppe Solgaard