Exposure and Overdue chart from invoices' Date, Due Date and Close Date

[1b0r90]

I have a settlement list as a table with the following data:

CustomerID	InvoiceNumber	Amount	Invoice Date	Due Date	Amount Settled	Closed Date
Customer1	Inv001	100	2020-01-01	2020-01-31	30	2020-01-25
Customer1	Inv001	100	2020-01-01	2020-01-31	70	2020-02-03
Customer2	Inv002	70	2020-01-01	2020-01-31	70	2020-02-01
Customer3	Inv004	50	2020-01-01	2020-01-15	10	2020-01-10
Customer3	Inv004	50	2020-01-01	2020-01-15	20	2020-01-12
Customer3	Inv004	50	2020-01-01	2020-01-15	20	2020-01-17
Customer3	Inv005	120	2020-01-01	2020-01-15	120	2020-01-17

As stated in the subject, I would like to create a chart that displays the exposure (oustanding amount) and overdue amount on a timescale.

Exposure is the sum of balance of invoices that are open at a given date (days between Invoice Date and Close Date).

Overdue is similar, but obviously from Due Date to Close Date.

For example Inv004 balance is 50 between 01-01 to 01-09, then 40 between 01-10 and 01-11, then 20 till 01-16. And this customer also has an invoice of 120 that they pay 2 days after the Due Date.

So for Customer3 the chart should look like this:

I also have a Date table that contains all dates from 2010 to 2030 so it can be referred to it in case this is needed.

Date table = ADDCOLUMNS (CALENDAR (DATE(2000,1,1), DATE(2025,12,31))

+ some columns to format the date column in various ways.

All data is available to tell exactly what is the exposure and the overdue, but so far I was unable to figure out how this should be done in Power BI.

I would really appreciate if someone could help me out on this problem.

[amitchandak]

@1b0r90 , see if HR Blog on the similar concept can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...