How to find oustanding invoices in sales report based on the ending balance of account receivable

[VanDuongLS]

Dear Sir/Madam, 

I have a problem that needs to be solved: How do I find overdue invoices in sales reports to calculate the overdue debt of each invoice?
I have 1 sales report table and 1 ending balance table as follows:

The ending balance of customer A or B is always the sum of invoices ordered from the newest date to the oldest date.

Can you please help me solve this problem? Thank you very much!

link of files: 

https://drive.google.com/drive/folders/1SXAchLesIHAxKmv2J0SlXW2okLkRaakR?usp=sharing

[Anonymous · Accepted solution]

Hi @VanDuongLS ,

Adavanced editor:

let
    Source = #"Sales report",
    #"Sorted Rows" = Table.Sort(Source,{{"Invoice date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Customer Name"}, 
    {{"end balance", 
    each 
    let
    table1 = Table.SelectRows(_,each [Outstanding]="x"),
    listdate = table1[#"Invoice date"],
    maxdate = List.Max(listdate),
    mindate = List.Min(listdate),
    result = if maxdate = mindate then List.Sum(Table.SelectRows(_,each [#"Invoice date"]=mindate)[#"Invoice Amt"]) else List.Sum(Table.SelectRows(_,each [#"Invoice date"]>mindate and [#"Invoice date"]<=maxdate)[#"Invoice Amt"])
    in
    result
    }})
in
    #"Grouped Rows"

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[VanDuongLS]

Can anyone help me, please...