Need help with amortization tables, accumulative.

Question

Hi, I really need help with this measure.&nbsp;I've tried to make this work but I don't find the way.&nbsp;So first of all my databases are in Spanish so I will try to change some labels so you can understand better.&nbsp;I have these two related databases&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;This is the general structure of my Tiempo database&nbsp;Client IDNameAccountPaymentMonths to liquidate1-101-225PACHECO FLORES MARIA&nbsp; EUGENIA1-3200-284RECURSOS PROPIOS501-102-23MARTINEZ HINOJOSA MIGUEL1-3200-24RECURSOS PROPIOS361-102-36MENDEZ VAZQUEZ GALDINO1-3200-50RECURSOS PROPIOS361-102-41JIMENEZ JALPA MARCO&nbsp; ANTONIO1-3200-55RECURSOS PROPIOS371-102-42RED DE TRANSPORTES S.A. DE C.V.1-3200-58RECURSOS PROPIOS371-102-42RED DE TRANSPORTES S.A. DE C.V.1-3200-57RECURSOS PROPIOS411-102-42RED DE TRANSPORTES S.A. DE C.V.1-3200-56RECURSOS PROPIOS381-102-45GONZALEZ CORTES LETICIA1-3200-338RECURSOS PROPIOS331-102-60ORTIZ AVILA RAMON1-3200-159RECURSOS PROPIOS491-102-60ORTIZ AVILA RAMON1-3200-83RECURSOS PROPIOS391-102-73SERRATOS RAMIREZ UBALDO&nbsp; ALFREDO1-3200-566CONGELADO51-102-73SERRATOS RAMIREZ UBALDO&nbsp; ALFREDO1-3200-103CONGELADO371-102-74SANCHEZ EVANGELISTA ALFREDO1-3200-106RECURSOS PROPIOS361-102-79VALENCIA HERNANDEZ PEDRO1-3200-180RECURSOS PROPIOS42&nbsp;And Consolidado table is an amortization table, with the next structure.&nbsp;&nbsp;Client Id&nbsp;Name&nbsp;&nbsp;Account&nbsp;&nbsp; Month&nbsp;&nbsp;&nbsp;Ends&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;Capital&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; io (Interest)&nbsp; &nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-200130/09/2014&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,651.04&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-200231/10/2014&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,314.62&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,471.10&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-200330/11/2014&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,641.91&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,143.81&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-200431/12/2014&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,499.72&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,286.00&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-200531/01/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,592.71&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,193.01&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-200628/02/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,374.54&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,411.18&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-200731/03/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,798.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,987.02&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-200830/04/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,120.06&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,665.66&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-200931/05/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,003.69&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,782.03&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-2001030/06/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,322.54&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,463.18&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-2001131/07/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,217.10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,568.62&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-2001231/08/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,324.92&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,460.80&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-2001330/09/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,639.83&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,145.89&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-2001431/10/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,551.52&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,234.20&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-2001530/11/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,863.66&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,922.06&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-2001631/12/2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,787.44&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,998.28&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-2001731/01/2016&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,907.04&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,878.68&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-2001829/02/2016&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,400.52&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,385.20&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-2001931/03/2016&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,161.40&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,624.32&nbsp;&nbsp;&nbsp;1-101-173&nbsp;&nbsp;CORTES RIVERA RUFINO&nbsp; ELISEO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-3100-2002030/04/2016&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,466.06&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,319.66&nbsp;&nbsp;&nbsp;and it goes on, so I have an amortization table for each client and account.&nbsp;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.&nbsp;I know I'm bad explaining myself I'm sorry.&nbsp;

v-xicai · Accepted Answer

Hi SamuelFTH&nbsp;,&nbsp;You can create column Accumulative_Interest using DAX below.&nbsp;Accumulative_Interest = CALCULATE(SUM(Consolidado[io (Interest)]),FILTER(ALLSELECTED(Consolidado),Consolidado[Month]&lt;=EARLIER(Consolidado[Month])))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Best Regards,Amy &nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Need help with amortization tables, accumulative.

1 Reply

Related content

Rolling accumulation

Rolling accumulation

Accumulated average

Dax logic for Amortization Calculation

loan amortization?

Client ID	Name	Account	Payment	Months to liquidate
1-101-225	PACHECO FLORES MARIA EUGENIA	1-3200-284	RECURSOS PROPIOS	50
1-102-23	MARTINEZ HINOJOSA MIGUEL	1-3200-24	RECURSOS PROPIOS	36
1-102-36	MENDEZ VAZQUEZ GALDINO	1-3200-50	RECURSOS PROPIOS	36
1-102-41	JIMENEZ JALPA MARCO ANTONIO	1-3200-55	RECURSOS PROPIOS	37
1-102-42	RED DE TRANSPORTES S.A. DE C.V.	1-3200-58	RECURSOS PROPIOS	37
1-102-42	RED DE TRANSPORTES S.A. DE C.V.	1-3200-57	RECURSOS PROPIOS	41
1-102-42	RED DE TRANSPORTES S.A. DE C.V.	1-3200-56	RECURSOS PROPIOS	38
1-102-45	GONZALEZ CORTES LETICIA	1-3200-338	RECURSOS PROPIOS	33
1-102-60	ORTIZ AVILA RAMON	1-3200-159	RECURSOS PROPIOS	49
1-102-60	ORTIZ AVILA RAMON	1-3200-83	RECURSOS PROPIOS	39
1-102-73	SERRATOS RAMIREZ UBALDO ALFREDO	1-3200-566	CONGELADO	5
1-102-73	SERRATOS RAMIREZ UBALDO ALFREDO	1-3200-103	CONGELADO	37
1-102-74	SANCHEZ EVANGELISTA ALFREDO	1-3200-106	RECURSOS PROPIOS	36
1-102-79	VALENCIA HERNANDEZ PEDRO	1-3200-180	RECURSOS PROPIOS	42

Client Id	Name	Account	Month	Ends	Capital	io (Interest)
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	1	30/09/2014	-	2,651.04
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	2	31/10/2014	4,314.62	7,471.10
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	3	30/11/2014	4,641.91	7,143.81
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	4	31/12/2014	4,499.72	7,286.00
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	5	31/01/2015	4,592.71	7,193.01
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	6	28/02/2015	5,374.54	6,411.18
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	7	31/03/2015	4,798.70	6,987.02
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	8	30/04/2015	5,120.06	6,665.66
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	9	31/05/2015	5,003.69	6,782.03
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	10	30/06/2015	5,322.54	6,463.18
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	11	31/07/2015	5,217.10	6,568.62
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	12	31/08/2015	5,324.92	6,460.80
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	13	30/09/2015	5,639.83	6,145.89
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	14	31/10/2015	5,551.52	6,234.20
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	15	30/11/2015	5,863.66	5,922.06
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	16	31/12/2015	5,787.44	5,998.28
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	17	31/01/2016	5,907.04	5,878.68
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	18	29/02/2016	6,400.52	5,385.20
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	19	31/03/2016	6,161.40	5,624.32
1-101-173	CORTES RIVERA RUFINO ELISEO	1-3100-200	20	30/04/2016	6,466.06	5,319.66