Aging

[Andrewutter12]

Hello,

I am attempting to create a Accounts Receivable table based on a table I already have within PowerBI. Within the table I have a journal column that shows the Sales Journal and Cash Receipt (invoice and payment on two different lines). I need to first set up a measure to deduct the receipt journal from the sales journal based on invoice number to determine if there is actually an outstanding receivable for that  client/invoice . Once this is determined, I need to then sort the outstanding receivables by the number of days it has been outstanding within standard buckets (e.g. 30, 60, 90, 120+). My end goal is to get a pie chart visual to show the amount of receivables within each bucket.

Please let me know if you can help.

[kumar27]

I have done the bucking thing in the sql query view where I have craeted a sql view for bucket dimension and then have inorporated it into the model and then have a FK PK join between the fact tabke and this bucketing dim.

Or else you can add a table in here if you do not have view acess and then join the table in model.

[edhans · Accepted solution]

Without sample data @Andrewutter12 this is hard to do as I don't fully understand your scenario. However, I mocked something up. I turn this:

Into this:

So my sample data has some invoices fully paid, partially paid, and not at all paid. I also assumed the due date was 30 days after the sales date. This is the M code to do it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvZS0lEyNDO2MADS5vrGhvpGBkYgtomBgYFSrE60kjOyCkugNEyFLlwJxBALIADSFvpwM4B6kM2AK0DYomtshmKGJRCArUG4wwikIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Journal = _t, #"Invoice Number" = _t, #"Transaction Date" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Date", type date}, {"Amount", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice Number"}, {{"Balance", each List.Sum([Amount]), type nullable number}, {"AllRows", each _, type table [Journal=nullable text, Invoice Number=nullable text, Transaction Date=nullable date, Amount=nullable number]}}),
    #"Added Date Due" = Table.AddColumn(#"Grouped Rows", "Date Due", each Date.AddDays(Table.SelectRows([AllRows], each [Journal] = "SJ")[Transaction Date]{0}, 30), type date),
    #"Added Days Past Due" = Table.AddColumn(#"Added Date Due", "Days Past Due", each Duration.Days(DateTime.Date(DateTime.LocalNow()) - [Date Due]), Int64.Type),
    #"Added Bucket" = Table.AddColumn(#"Added Days Past Due", "Bucket", each if [Days Past Due] < 31 then "Current"
else if [Days Past Due] < 61 then "31-60"
else if [Days Past Due] < 91 then "61-90" 
else "90+ days", type text),
    #"Filtered Rows" = Table.SelectRows(#"Added Bucket", each ([Balance] <> 0)),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Filtered Rows", "AllRows", {"Journal"}, {"Journal"})
in
    #"Expanded AllRows"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If you need more help @Andrewutter12 please give us some sample data and expected output that we can use. Screenshots are fine for expected output, but not for sample data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Andrewutter12]

Thank you for your help on this! This solution appeared to work!

[edhans]

Great @Andrewutter12 - glad to be of assistance. As a CPA, I've done quite a few AP and AR aging reports. 😂