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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jtemes
Helper I
Helper I

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

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
Helper I
Helper I

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

This is perfect. Thank you!

jtemes
Helper I
Helper I

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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