Nov 19 2021 12:30 AM
Nov 19 2021 01:10 AM
Thanks @Riny_van_Eekelen. I would also like to be adding a certain percentage to the running totals as an accumulation (Coumpounding). So the opening values plus a % interest and add that months amount son on and so forth. Apologies for incomplete infor. see attached.
Nov 19 2021 01:19 AM
@mmtawali So you are not talking about Scores but Amounts owed, or something similar. What logic/system sits behind the percentages? Starting with 10% the first 4 days, then three days at 15%, and then down to 8%. and after that?
Better to upload a file that contains data that truly represents the data you are working with in real life. Remove any private or confidential information, though.
Nov 19 2021 02:02 AM
Nov 19 2021 04:41 AMSolution
@mmtawali Now I get you. Then PowerQuery could be the tool to help you, though it's not very straight-forward, due to the fact that not every person has a transaction on each day, but you want to accrue the rates on a daily basis on the running totals per person. The attached file contains several queries that ultimately come up with the answers as you included on the summary sheet.
Not sure though how this file will perform with 30K names and 5 years of transactions, as you will be looking at a potential of 55 million records to begin with, if I calculated correctly. Or can you split yur data into smaller groups? Perhaps it can be optimized but not by me. Sorry!