Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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))
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
@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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |