Displaying total outstanding debt per day based on start and end date

[djohanlon]

I have some data which contains the following (example):

Issue Date, Amount, Maturity Date

01.01.2024, 10,000, 20.10.2024

05.01.2024, 12,000, 15.10.2024

08.01.2024, 8,000, 24.10.2024

etc.

so on the 06.02.2024, it would show that there was 22,00 outstanding. on the10.01.2024, it would show that there was 30,000 outsanding. On the 16.01.2024, this will now only show 18,000. and so forth.

What I want to do is create a table or graph which will show me, on any given date, how much is outstanding.

For any given date, Outstanding is how much, and the amount, were issued on or before that date (preferably midnight to midnight) and the maturity is on or after that date(preferably midnight to midnight).

[v-kongfanf-msft · Accepted solution]

Thanks for the reply from @amitchandak , please allow me to provide another insight:

Hi @djohanlon ,

You need to create a table of dates without a relationship and then try the following formula:

Calendar = 
SELECTCOLUMNS (
    CALENDAR(DATE(2024, 1, 1), DATE(2024, 10, 20)),
    "DateKey", [Date],
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month Name", FORMAT ( [Date], "MMMM" ),
    "Day", DAY ( [Date] ),
    "Day of Week", WEEKDAY ( [Date] )
)

Outstanding Debt = 
CALCULATE (
    SUM ( 'Debt'[Amount] ),
    FILTER (
        'Debt',
        'Debt'[Issue Date] <= MAX ( 'Calendar'[Date] )
            && 'Debt'[Maturity Date] >= MAX ( 'Calendar'[Date] )
    )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@djohanlon , I think same approach as Active/current employee should help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU