Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 calcu...
Need Help
MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    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.