cancel
Showing results for
Did you mean:
Frequent Visitor

Balance at any date in an Amoritzation table

Hi all

I have an amortization table of a loan with 2 columns, one with dates and other one with the outstanding balance of a loan (see table below)

Dates                                   Outstanding Balance

10/01/2020                          100.000

10/02/2020                           80.000

10/03/2020                           60.000

10/04/2020                           40.000

10/05/2020                           20.000

10/06/2020                                    0

As I have many table like this, with different dates, I want to know the outstanding balance of all the loans at any possible date using a filter. So, if I put in the filter 31/03/2020, for this table, I should be getting 60.000

1 ACCEPTED SOLUTION
Super User IV

@jtemes , Try this measure with an independent date table

new measure =
var _max = maxx(allselected(Date), Date[Date] )
return
calculate(sum(Table[Outstanding Balance]), filter(Table, Table[Initial Date] <=_max && Table[End Date] >=_max))

Proud to be a Super User!

6 REPLIES 6
Community Support

Hi @jtemes ,

It seems that you have get a solution. Would you please try to accpet the useful reply as answer to help others with the same issue find it more quickly.

Best Regards,

Dedmon Dai

Frequent Visitor

@amitchandak maybe if I put the table in this format is clearer why the balance as of 31/03/2020 is 60.000

Initial Date       End Date                Balance

10/01/2020      09/02/2020            100.000

10/02/2020      09/03/2020              80.000

10/03/2020      09/04/2020              60.000

10/04/2020      09/05/2020              40.000

10/05/2020      09/06/2020              20.000

10/06/2020      10/06/2020                       0

How would you solve it with a table like this?

Super User IV

@jtemes , Try this measure with an independent date table

new measure =
var _max = maxx(allselected(Date), Date[Date] )
return
calculate(sum(Table[Outstanding Balance]), filter(Table, Table[Initial Date] <=_max && Table[End Date] >=_max))

Proud to be a Super User!

Frequent Visitor

This is perfect. Thank you!

Frequent Visitor

The message was not posted in full and it cut the last paragraph.

My question is what should I do to get this filter work as I mention. I have calendar table linked to the column Dates but if I pick up a date different than the onw shown in the table, it does not return anything.

Thanks

Super User IV

@jtemes , I am not clear, how it can be 60000 on 31/03/2020

ok if it the case when date is missing

you can try a measure

new measure =
var _max = maxx(filter(Date, Date[Date] <= selectedvalues(Date[Date])),Date[Date])
return
calculate(sum(Table[Outstanding Balance]), filter(All(Date), Date[date] = _max))

with independent date table

new measure =
var _max = maxx(filter(Date, Date[Date] <= selectedvalues(Date[Date])),Date[Date])
return
calculate(sum(Table[Outstanding Balance]), filter(Table, Table[date] = _max))

if you need by each load id(max date), different formula is needed

Proud to be a Super User!

Announcements