Calculation help, data from multiple tables

[maasool]

I have three tables:

1. Table PRICES with multiple ISIN's (securities) and their PRICE by each DATE;
2. Table, where are number of securities (AMOUNT) by each ISIN per different PORTFOLIO. In this table, I have amounts on dates, when there have been changes. For example, on 09.10.2023, three new ISIN's were added to the portfolio each 1000 units. There have been no changes afterwards, so the number of units by each ISIN is the same on 24.10.2023. However, this is not in the table, as table records only changes.
3. Calendar table.

I'd like to create a power pivot, where I could have AMOUNT of securities and their value (AMOUNT * PRICE) at any random date by different portfolios. However, I can't manage to get it to work. I managed to calculate Balance per ISIN per portfolio for all dates (used TOTALYTD), but I can't figure out what to do with prices, because for prices, I need to get exact price on that date and exact price i need to calculate the values for different dates.

Thank you for help!

[Anonymous]

Hi @maasool ,

I think you might need to use a date table and a measure to create a power pivot with amount and value of securities by portfolio and date in Power BI. You can use a date table to filter and group your data by different time periods, and use a measure to calculate the value of securities by multiplying the amount and price columns. You can also use time intelligence functions in DAX to compare values across different dates.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...