Calculate portfolio value based on purchase/sell orders

[Anonymous]

I've been studying Power BI and nothing better than bulding something to solve a personal issue. The problem is, I am unsure if my data structure is good enough to solve the issue. I have 4 tables:

1. Funds - contains the ID and Name of investment funds
2. Daily Reports - contains daily reports of quota value for each Fund from (1)
3. Portfolios - contains Name, Fund ID, Date, and Amount of purchase/sell (orders) of each Fund from (1)
4. Dates - date table

I am trying to build a measure that will calculate the day-to-day value of each investment fund (quota * amount) whenever the daily report date is greater than or equal to that row from the portfolio table.

In other words, if my Portfolio table contains: Amount of 3,56 @ 20/12/2018 and Amount of -2,98 @ 12/02/2019, I would use the daily quota to calculate something like Summation of ( quota * amount where daily quota greater than Order date ). In my prior example, would result in 3,56 * daily quota value between 20/12/2018~11/02/2019, 0,58 * daily quota value after 12/02/2019, and 0 before 20/12/2018.

Is there a way of creating such measure?

My pbix > https://1drv.ms/u/s!Aon_lf2n7StXi49n6TYf-dOok9etiA?e=2k6EAx

I also understand that my data structure could possibly be wrong. TIA

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could use this formula

daily quota value = IF(MAX(Dates[Date])<MIN(Portfolios[Date]),0, CALCULATE(SUM(Portfolios[Amount]),FILTER(Portfolios,Portfolios[Date]<=MAX('Daily Reports'[Date]))))

And for the relatinship between Portfoloos and Daily Reports should be Both

Result:

Best Regards,

Lin