cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rimgrs
Helper I
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 noDocument DateAccAmount
AA12020-09-13645230
AA12020-09-206452-30

 

The result of this document should look like:

DateDoc noACCReceivables
2020-09-13AA1645230
2020-09-14AA1645230
2020-09-15AA1645230
2020-09-16AA1645230
2020-09-17AA1645230
2020-09-18AA1645230
2020-09-19AA1645230

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!

1 ACCEPTED SOLUTION

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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
MFelix
Super User III
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:

 

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

 

MFelix_0-1606998057260.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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
Super User III
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.