Forum Discussion
Calculating annual (& YTD) account credits from a dated record of monthly credits
Hi...Been struggling to solve the following problem with Excel 365 and hoping someone can help. Here is a summary. Thanks in advance for any help and suggestions. Fraser.
There 20 different accounts. Since the particular date on which each account was opened, a payment is made to the account on the first of every month thereafter without fail. The date on which each account was opened, together with the record of corresponding monthly payments are available in a table, together with the date and change to the monthly payment. Future monthly payment changes are also recorded in the table. The problem is how to populate a table summarising the amount paid into each account in each of the preceding calendar years and in the current calendar year to date. A sample table with a reduced number of accounts and data is shown below. Dates are dd/mm/yyyy
| Date/Account | AC01 | AC02 | AC03 | AC04 |
| 01/02/2016 | £650 | |||
| 01/05/2016 | £65 | £50 | ||
| 01/02/2017 | £100 | £15 | ||
| 01/05/2017 | £200 | £75 | ||
| 01/11/2018 | £20 | |||
| 01/03/2020 | £35 | |||
| 01/06/2021 | £660 | |||
| 01/06/2023 | £50 | |||
| 01/02/2024 | £700 | |||
| Credits pa/YTD | AC01 | AC02 | AC03 | AC04 |
| 2016 | ||||
| 2017 | ||||
| 2018 | ||||
| 2019 | ||||
| 2020 | ||||
| 2021 | ||||
| 2022 | ||||
| 2023 |
Better ?
EDIT: Added variant - A bit more straightforward (kudos @https://answers.microsoft.com/en-us/profile/2d3aa4c3-5161-4914-86a7-b9d2f179b3a8 for the approach - https://answers.microsoft.com/en-us/msoffice/forum/all/power-query-only-how-to-multiply-a-single-column/34e9826c-d459-4451-bd2e-f6c490474203)
13 Replies
- Hi Lz.... Many thanks for taking time to reply and for recommending using a Pivot Table. I realise and apologise that I have not explained the problem clearly enough. Please see my reply to Quadruple Pawn which gives further information. Thanks again for your help. Fraser
Better ?
EDIT: Added variant - A bit more straightforward (kudos @https://answers.microsoft.com/en-us/profile/2d3aa4c3-5161-4914-86a7-b9d2f179b3a8 for the approach - https://answers.microsoft.com/en-us/msoffice/forum/all/power-query-only-how-to-multiply-a-single-column/34e9826c-d459-4451-bd2e-f6c490474203)
=VSTACK({""."AC01"."AC02"."AC03"."AC04"},HSTACK(UNIQUE(YEAR(H2:H10)),MAKEARRAY(COUNTA(UNIQUE(YEAR(H2:H10))),COUNTA(I1:L1),LAMBDA(r,c,SUM(FILTER(CHOOSECOLS(I2:L10,c),YEAR(H2:H10)=INDEX(UNIQUE(YEAR(H2:H10)),r)))))))You can try this formula.
- Hi QP....Thank you very much for taking time to reply. The method looks really interesting. However, I realise and must apologise that I have not explained the problem clearly enough. To clarify, for example for AC01; The account was opened on 010216 with a payment of £650 per month. Payments of £650 per month were made on the first of each successive month until 010621 when the monthly payment was increased to £660 per month. This new monthly payment of £660 continued every month thereafter. The intention is increase the monthly payment to £700 on 010224. This would mean that the annual totals for each of the years from 2016-2022 would be the sum of all the monthly credits in each of those years; eg for 2016 the sum would be 11 x £650 = £7150. For the current year, the annual total would depend on the current date and for dates in the future the total would be displayed as zero until that date had occurred. The same applies to all the other accounts. I hope that is clearer. Many thanks Fraser
You can try Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
