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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Calculate outstanding NPA loan amount over date

Hi guys,

I have a table as shown below and I have to calculate outstanding loan amount over time (date format - mm/dd/yyyy)


For eg. in this case from 8/2/2022 till 9/2/2022 outstanding amout will be $100, on 9/3/2022 the out standing amount will become $250 on 9/6/2022 it will be $300 and accordingy it will reduce to $200 on 9/25/2022(amount recovered) and to $150 on 10/2/2022.

Null recovery date represents that this amount is outstanding as of TODAY().


I want to represent this on a column chart with days->months->year heirrarchy on x-axis and outstanding amount on y-axis.


Can someone please help me out in this issue?







Hi Hoang,

Thanks for your response, but I found another way to get this calculation done

View solution in original post

Solution Specialist
Solution Specialist

Hi, assume you have a Date column to choose period make calculate


NPA loan = 

var sum =SUM([Outstanding amount])
var maxdate = MAX([date column])

var NPA = CALCULATE (sum,[NPA date]<=maxdate)

var Recovery = CALCULATE (sum,[Recovery date]<=maxdate,[Recovery date] <>Blank())

return NPA +Recovery

Hi Hoang,

Thanks for your response, but I found another way to get this calculation done

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors