cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jtemes
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

@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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

6 REPLIES 6
v-deddai1-msft
Community Support
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

jtemes
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? 

Thanks for your help

@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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

This is perfect. Thank you!

jtemes
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

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors