Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SamuelFTH
Regular Visitor

Need help with amortization tables, accumulative.

Hi, I really need help with this measure.

 

I've tried to make this work but I don't find the way.

 

So first of all my databases are in Spanish so I will try to change some labels so you can understand better.

 

I have these two related databases 

Related_Databases.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This is the general structure of my Tiempo database

 

Client IDNameAccountPaymentMonths to liquidate
1-101-225PACHECO FLORES MARIA  EUGENIA1-3200-284RECURSOS PROPIOS50
1-102-23MARTINEZ HINOJOSA MIGUEL1-3200-24RECURSOS PROPIOS36
1-102-36MENDEZ VAZQUEZ GALDINO1-3200-50RECURSOS PROPIOS36
1-102-41JIMENEZ JALPA MARCO  ANTONIO1-3200-55RECURSOS PROPIOS37
1-102-42RED DE TRANSPORTES S.A. DE C.V.1-3200-58RECURSOS PROPIOS37
1-102-42RED DE TRANSPORTES S.A. DE C.V.1-3200-57RECURSOS PROPIOS41
1-102-42RED DE TRANSPORTES S.A. DE C.V.1-3200-56RECURSOS PROPIOS38
1-102-45GONZALEZ CORTES LETICIA1-3200-338RECURSOS PROPIOS33
1-102-60ORTIZ AVILA RAMON1-3200-159RECURSOS PROPIOS49
1-102-60ORTIZ AVILA RAMON1-3200-83RECURSOS PROPIOS39
1-102-73SERRATOS RAMIREZ UBALDO  ALFREDO1-3200-566CONGELADO5
1-102-73SERRATOS RAMIREZ UBALDO  ALFREDO1-3200-103CONGELADO37
1-102-74SANCHEZ EVANGELISTA ALFREDO1-3200-106RECURSOS PROPIOS36
1-102-79VALENCIA HERNANDEZ PEDRO1-3200-180RECURSOS PROPIOS42

 

And Consolidado table is an amortization table, with the next structure.

 

 Client Id Name  Account   Month   Ends        Capital       io (Interest)    
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-200130/09/2014                   -        2,651.04  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-200231/10/2014         4,314.62      7,471.10  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-200330/11/2014         4,641.91      7,143.81  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-200431/12/2014         4,499.72      7,286.00  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-200531/01/2015         4,592.71      7,193.01  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-200628/02/2015         5,374.54      6,411.18  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-200731/03/2015         4,798.70      6,987.02  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-200830/04/2015         5,120.06      6,665.66  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-200931/05/2015         5,003.69      6,782.03  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-2001030/06/2015         5,322.54      6,463.18  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-2001131/07/2015         5,217.10      6,568.62  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-2001231/08/2015         5,324.92      6,460.80  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-2001330/09/2015         5,639.83      6,145.89  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-2001431/10/2015         5,551.52      6,234.20  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-2001530/11/2015         5,863.66      5,922.06  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-2001631/12/2015         5,787.44      5,998.28  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-2001731/01/2016         5,907.04      5,878.68  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-2001829/02/2016         6,400.52      5,385.20  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-2001931/03/2016         6,161.40      5,624.32  
 1-101-173  CORTES RIVERA RUFINO  ELISEO              1-3100-2002030/04/2016         6,466.06      5,319.66  

 

and it goes on, so I have an amortization table for each client and account.

 

what I need to do is sum the interest(IO in Consolidado database) of the months(Month in Consolidado database) it took to liquidate(Months to liquidate in Tiempo Database), because I need to know how much did I gain with that loan.

 

I know I'm bad explaining myself I'm sorry.

 

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

Hi @SamuelFTH ,

 

You can create column Accumulative_Interest using DAX below.

 

Accumulative_Interest = CALCULATE(SUM(Consolidado[io (Interest)]),FILTER(ALLSELECTED(Consolidado),Consolidado[Month]<=EARLIER(Consolidado[Month])))

 

7.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-xicai
Community Support
Community Support

Hi @SamuelFTH ,

 

You can create column Accumulative_Interest using DAX below.

 

Accumulative_Interest = CALCULATE(SUM(Consolidado[io (Interest)]),FILTER(ALLSELECTED(Consolidado),Consolidado[Month]<=EARLIER(Consolidado[Month])))

 

7.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.