PowerBi not paid invoices

[Mike1309]

Hello team 

I'm a beginner in PowerBi and I need to build something that is too complex for me:

using PowerQuery I've got a table with

invoice number,

customer,

date of issue 

date payment (when the invoice was paid (empty if not paid))

I need to create a report:

how many invoices were unpaid at the end of every week  (Sunday), grouped by Customer? 

for example 

#week	customer	# unpaid invoices
2023-week12	alfa	10
2023-week12	beta	2
2023-week13	alfa	3
2023-week13	beta	20

could you please help me ?

Thank you in advance

[tamerj1 · Accepted solution]

Hi @Mike1309 

the week column should be part of a disconnected 'Date' table.

Unpaid Orders =
VAR MaxDate =
MAX ( 'Date'[Date] )
RETURN
COUNTROWS (
FILTER (
'Table',
'Table'[date of issue] <= MaxDate
&& COALESCE ( 'Table'[date payment], TODAY () ) > MaxDate
)
)