Forum Discussion

Copper Contributor

May 14, 2023

Solved

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 accou...

Formulas and Functions

Lorenzo
May 14, 2023
PhraserB
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)
SAMPLE_UnpivotAccountFilledPeriods.xlsx24 KB
SAMPLE_UnpivotAccountFilledPeriods_Variant.xlsx25 KB

Lorenzo

Silver Contributor

May 14, 2023

Hi PhraserB

EDIT: After clarifications below proposal does not apply. Approach could apply to other scenario though

Alternatively Unpivot your accounts with Get & Transform aka Power Query (sample attached). After that almost all analysis are easy, i.e. with a PivotTable:

SAMPLE_UnpivotAccount.xlsx19 KB

PhraserB

Copper Contributor

May 14, 2023

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

Lorenzo
Silver Contributor
May 14, 2023
PhraserB
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)
SAMPLE_UnpivotAccountFilledPeriods.xlsx24 KB
SAMPLE_UnpivotAccountFilledPeriods_Variant.xlsx25 KB
- PhraserB
  Copper Contributor
  May 14, 2023
  Hi Lz... Thanks. This is very neat and exactly what I was looking for. Will have a close look tomorrow when I have more time. Thanks again. Brilliant! Regards Fraser
  - Lorenzo
    Silver Contributor
    May 15, 2023
    Hi PhraserB
    Please re-download (inc. Variant). I made a couple of adjustments (same output) - I'll explain later if necessary
    
    Did a couple of scalability checks with 50 account running until end of 2050 - works like a charm
    FYI: 1st time you Refresh a query within an Excel session takes a bit of more time - to load the .NET Framework. Ex.: with the above sample 1st time to Refresh +/- 5 secs, next Refresh +/- 1 sec. (here)