Problem with Dax Data/Measures Model - Total Accumulated Valuation x Value (depending on period)

[Syndicate_Admin]

Good morning, everybody.

I'm a little new in the community and with Power BI. I hope you can help me solve this inconvenience to which I have given many turns and have not been able to solve it.

The model attempts to relate mainly 2 fact tables: a table of investment movements (to which other tables with different dimensions are added - instrument, company, type of movement, etc.-) and on the other hand a price table of the instruments by period (year, month). The idea is to record the movements of an investment portfolio, making a total cumulative period (this I could do without problem) of the "stock" of each investment, but I have drawbacks when it comes to multiplying that total accumulated by the value of the other fact table (in order to put together different results by period), since it has different graininess compared to the first (in the table of movements , it is crucial to see the investments for each of the dimensions that the model has, while the price table is only related to the instrument and the period). The problem arises when the movement table has no data in a certain period (but if there is a cumulative stock) and there is a price to value that stock. The model seems to take only the quote when there is a movement in the period. Another problem I see is that when the filter is applied per company (required dimension) this causes drawbacks to relate the quote data (which does not have the company dimension).

I've tried putting together a key, putting together a combination of the movements+quotes tables and I haven't achieved positive results. For example, I detail the table where there is a stock for "Balanz Class B shares" with the company filter "Terminal Zarate" and a price for the period, but the model does not take it:

This is the entry table, where the price for that period is displayed (but it does not have company dimensions or transaction data, only the quote):

The "Current Valuation" measure is as follows:

I detail the model:

In this model, the entry table has combined the quote by key (nameFCI+year+month). As you can see, in the entries table, there are periods where the key has no entries, but if quoted:

I can upload the pbix file to you.

Thank you very much already!

Best regards. -

[DataInsights · Accepted solution]

@leandro_salas24,

Will you be able to upload the pbix file or provide a link?

Thank you.