cancel
Showing results for
Did you mean:
Highlighted
leoxhc Frequent Visitor

Measure to calculate current fixed income

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

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

Re: Measure to calculate current fixed income

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: Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Community Support Team

Re: Measure to calculate current fixed income

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: 