Calculating Funds Under Management - Sum of Product

[Anonymous]

Hi there,

I'm working on a Funds Under Management model and have hit somewhat of a roadblock and what appreciate some help in solving the required DAX.

We have a model similar to the one below (The example is quite simplified):

An explanation of the tables:

• Transactions: This is really the fact table, with the transactions, account no, transaction date and the running total as it would stand after the transaction.
• Investor: This is the investor dimension, which would be used for grouping of accounts, multiple accounts can belong to the same investor.
• Funds: This is the funds dimension, an account can only have one fund.
• Fund Price: This table list the price of the funds on a given date, we don't always have a new price for every calendar date.
• Reporting Date: This table would be used to report on the Funds Under Management at any given date.

The calculation of Funds Under Management can be done via two measure (not sure on this?):

1. Summing the result of all the products of the last known running total of an account (from Transactions) and last known price for the corresponding fund (from Funds Price) at any given date (from Reporting Date)
2. Do the same as method 1 but first sum the last known running totals grouped by the fund and then multiply by the last known price for the fund

These are my thoughts on going on about this, not sure if I'm doing this correctly or not, any and all help would be appreciated.

PBIX file with sample data: https://1drv.ms/u/s!AgRCl9UMoU6bgk-3d02tQqOA48oU

TIA

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

What is your desired reuslt? I have tested for your first problem but I could understand other issues:

and last known price for the corresponding fund (from Funds Price) at any given date (from Reporting Date)

Do the same as method 1 but first sum the last known running totals grouped by the fund and then multiply by the last known price for the fund

For the first issue about Summing the result of all the products of the last known running total of an account (from Transactions):

Create a Rank column:

Rank = COUNTROWS(FILTER('Transactions','Transactions'[Transaction Date]<=EARLIER(Transactions[Transaction Date])&&'Transactions'[Account]=EARLIER(Transactions[Account])))

Create below measures:

Measure = IF(CALCULATE(SUM(Transactions[Rank]))=CALCULATE(MAX('Transactions'[Rank]),FILTER(ALL('Transactions'),'Transactions'[Account]=MAX('Transactions'[Account]))),CALCULATE(SUM(Transactions[Running Total])),BLANK())

Sum = SUMX('Transactions',[Measure])

Result:

Could you please offer me more information and post your desired result if possible?

Regards,

Daniel He