cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
leoxhc Frequent Visitor
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    
dateCoefficient DateSymbolAllocationTypeAmount
01/01/20191,02 01/05/2019ABCFIBuy$ 10.000,00
01/02/20191,02 01/09/2019DEFFIBuy$   5.000,00
01/03/20191,02      
01/04/20191,02      
01/05/20191,02      
01/06/20191,02      
01/07/20191,02      
01/08/20191,02      
01/09/20191,02      
01/10/20191,02      
01/11/20191,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:

SymbolCurrent value
ABC $ 11.486,86
DEF $   5.306,04

 

AllocationCurrent value
FI $ 16.792,90

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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:

1.PNG

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

 

 

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.
1 REPLY 1
Community Support Team
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:

1.PNG

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

 

 

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.