Calculate line chart of unpaid invoices for dates in the past

[burny81]

Hello,

I am attempting to create a line chart for the last two years in which the sum of the unpaid invoices is shown, wherein the line chart shows the amount of unpaid invoices at that date in the past.

I have an invoice table having the "invoicing date", the "invoice amount" and the "paid date".

I created a date table with the dates of the last two years and wanted to create an additional column in the date table having the sum of unpaid invoices for the respective date.

The data looks like this:

Invoices Table
Invoice Number	Invoice Amount	Invoicing Date	Paid Date
1	$          300,00	2022-03-01	2022-03-07
2	$          500,00	2022-03-02	2022-03-10
3	$          250,00	2022-03-03	2022-04-01
4	$          150,00	2022-03-04	2022-03-05
5	$          700,00	2022-03-04	2022-03-10

Date Table
Date
01.01.2020
02.01.2020
03.01.2020
04.01.2020
05.01.2020
…
30.12.2022

The result that I expect should e.g. be $300 for 2022-3-1, $0 for 2022-2-28 or $250 for each date between 2022-3-10 and 2022-3-31. 

To get these results I believe I have to do two comparisons:

1. invoice table[invoicing date] <= date table[date]

&&

2. invoice table[paid date] > date table[date]

Then I have to SUM all rows for invoice table[invoice amount] that remain (which should be the amount of all invoices that had been sent out but had not yet been paid at that specific date in the past).

My problem is that Power Bi does not allow me to do the two comparisons because the data is from two differnt tables.

What can I do?

Thank you very much!

Best regards

[v-xiaotang · Accepted solution]

Hi @burny81 

Thanks for reaching out to us.

please try this measure

sum of unpaid invoices = SUMX(FILTER('Table','Table'[Invoicing Date]<=MIN('calendar'[Date]) && 'Table'[Paid Date]>MIN('calendar'[Date])),[Invoice Amount])

Best Regards,

Community Support Team _Tang

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

[Dinesh_Suranga]

@burny81 

Hi,

Could you please share some sample data?

 Thank you 

[burny81]

Hi, 

I added the sample data in the original post.

Best regard