cancel
Showing results for
Did you mean:
Helper I

## Split rows by date in Power Query with running total condition

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
in
// 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}}),
Date.From (DateTime.FixedLocalNow ()),-1) else Date.AddDays([MAX date],-1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"MAX date", "Summ"}),
#"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:

Thanx!

1 ACCEPTED SOLUTION
Super User II

Hi @rimgrs ,

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

Memory efficient clustered running total in Power BI – The BIccountant

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

4 REPLIES 4
Super User III

Hi @rimgrs ,

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:

``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:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

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.

Super User III

Hi @rimgrs ,

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User II

Hi @rimgrs ,

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

Memory efficient clustered running total in Power BI – The BIccountant

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Announcements