Split rows by date in Power Query with running total condition

[Anonymous]

Hi, guys,

I've tried to create a table in Power Query which gives me a Receivable amount by document and date. It works fine with a small scale of data, but I need help with adjusting these calculations to a larger scale of data (500-1000 k rows in General Ledger table)?

My data is from General Ledger table, positive amount - invoice, negative amount - payment:

Document no	Document Date	Acc	Amount
AA1	2020-09-13	6452	30
AA1	2020-09-20	6452	-30

The result of this document should look like:

Date	Doc no	ACC	Receivables
2020-09-13	AA1	6452	30
2020-09-14	AA1	6452	30
2020-09-15	AA1	6452	30
2020-09-16	AA1	6452	30
2020-09-17	AA1	6452	30
2020-09-18	AA1	6452	30
2020-09-19	AA1	6452	30

First date is Invoice date and last date payment date -1 day (Customer made payment in 20th of September, so in this day this document is fully paid). There are some cases where there is more than one payment, so in the Receivables column should be the balance of receivables for particular date and particular document. There are documents that isn’t pay fully for today, for them the ending date is today.

So, to calculate this I've made two table:

Calculating running total for the document - General Ledger (running)

let
    Source = #"General Ledger",
    #"Sorted Rows" = Table.Sort(Source,{{"Document Date", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Custom"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Document no"}, {{"Count", each _, type table [Document no=nullable text, Document Date=nullable date, Acc=nullable number, Amount=nullable number]}}),
 //Function calculate Running total
 RunFuncion = (RunTable as table) as table =>
 let 
     #"Added Index" = Table.AddIndexColumn(RunTable, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum (List.Range(#"Added Index"[Amount],0,[Index])))
in
    #"Added Custom",
// Call the function
RunTotals = Table.TransformColumns (#"Grouped Rows", {"Count", each RunFuncion(_)}),
    #"Expanded Count" = Table.ExpandTableColumn(RunTotals, "Count", {"Document Date", "Acc", "Amount", "Custom"}, {"Count.Document Date", "Count.Acc", "Count.Amount", "Count.Custom"})

in
    #"Expanded Count"

And then created another table that expands document by dates (General Ledger (dates)) and merges the running total for every day (General Ledger (running)).

let
    Source = #"General Ledger",
    #"Grouped Rows" = Table.Group(Source, {"Document no"}, {{"MIN date", each List.Min([Document Date]), type nullable date}, {"MAX date", each List.Max([Document Date]), type nullable date}, {"Summ", each List.Sum([Amount]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Ending date", each if [Summ]>0 then Date.AddDays(
Date.From (DateTime.FixedLocalNow ()),-1) else Date.AddDays([MAX date],-1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"MAX date", "Summ"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each {Number.From([MIN date])..Number.From ([Ending date])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Document no", "MIN date"}, #"General Ledger (running)", {"Document no", "Count.Document Date"}, "General Ledger (running)", JoinKind.LeftOuter),
    #"Expanded General Ledger (running)" = Table.ExpandTableColumn(#"Merged Queries", "General Ledger (running)", {"Count.Custom"}, {"General Ledger (running).Count.Custom"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded General Ledger (running)",{"MIN date", "Ending date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Date"}, {"General Ledger (running).Count.Custom", "Receivable"}})
in
    #"Renamed Columns"

I'm not very experience in Power Query functions, so I really need your help.

How can I make these calculations simpler?

The example of pbix is here:

https://drive.google.com/file/d/1AjhbHU7qn4O-12OvNyFbqmX0uOfERb7k/view?usp=sharing

Thanx!

[MFelix]

Hi @Anonymous ,

Believe that there is no need to have a new table or column with this values if you want to have the calculation of the documents by date then you simply need to do a cumulative sum of the values at the selected date, adding more table or columns to your model will decrease performance and more difficulty in future developments.

I would do the following:

• Add a calendar table
• Add the following measure:

Receivables = CALCULATE(SUM('General Ledger'[Amount]); FILTER(ALLSELECTED('General Ledger'[Document Date]); 'General Ledger'[Document Date]<= SELECTEDVALUE('Calendar'[Date])))

Final result below and in attach PBIX file:

[Anonymous]

Thanx, @MFelix ,but it's not the only case. Mainly this table is for calculating receivables aging, because when I have 1 mln. row it quite difficult to have quick calculations about debt aging dynamics and overdue debts for historical dates.

[MFelix]

Hi @Anonymous ,

Not really sure if this is the best approach for your model since you will be replicating the model into several millions lines more.

Calling out to @ImkeF  the best M language expert out here she can give you a hand on optimizing this this on power query for sure.

[ImkeF · Accepted solution]

Hi @Anonymous ,

you have to use this approach to make it run faster:

Memory efficient clustered running total in Power BI – The BIccountant