Measure to calculate current fixed income

[leoxhc]

Hello, I need to calculate a product of coefficients from the day of purchase to the most current available date for each row and then multiply by a value. For instance, I have the following tables:

table 1			table 2
date	Coefficient		Date	Symbol	Allocation	Type	Amount
01/01/2019	1,02		01/05/2019	ABC	FI	Buy	$ 10.000,00
01/02/2019	1,02		01/09/2019	DEF	FI	Buy	$   5.000,00
01/03/2019	1,02
01/04/2019	1,02
01/05/2019	1,02
01/06/2019	1,02
01/07/2019	1,02
01/08/2019	1,02
01/09/2019	1,02
01/10/2019	1,02
01/11/2019	1,02

So, in this case, the measure called Current Value must to calculate product of column coefficient from day 01/05/19 until 01/11/19 for symbol ABC and calculate product of column coefficient from day 01/09/19 until 01/11/19 for symbol DEF, then multiply such product by respective amount for each symbol and when a matrix is filtered by Allocation the total amount of measure Current Value must totalize the sum of current value, like shown below:

Symbol	Current value
ABC	$ 11.486,86
DEF	$   5.306,04

Allocation	Current value
FI	$ 16.792,90

Thanks in advance.

[v-danhe-msft · Accepted solution]

Hi @leoxhc,

Based on my test, you could refer to below measures:

Measure = CALCULATE(PRODUCTX('Table1',Table1[Coefficient]),FILTER(ALL('Table1'),'Table1'[date]>=MAX('Table2'[Date])))*CALCULATE(MAX('Table2'[Amount]))

Sumx = SUMX('Table1','Table1'[Measure])

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He