Forum Discussion
Adding Custom Column To Obtain Prior Month Balance
JRParker my bad. I wanted to sort by date upon grouping but then changed my mind... Before I give up and commit a suicide, lets replace function f with the following
f = (tbl as table) as table => [sorted = Table.Sort(tbl, "Date"), // Sort the table by the "Date" column prior_month = {0} & List.RemoveLastN(sorted[Balance], 1), // Create a list of prior month balances by removing the last balance value and appending a 0 at the beginning out = Table.FromColumns(Table.ToColumns(sorted) & {prior_month}, Table.ColumnNames(sorted) & {"Prior Month"}) // Add the prior month balances as a new column named "Prior Month" ] [out]
hi, JRParker group your data by Entity and Account Number and then do whatever you want to each group of data. If you have 1 balance per month per every Entity & Account then try this
let
Source = your_table,
f = (tbl as table) as table =>
[sorted = Table.Sort(tbl, "Date"),
prior_month = {0} & List.RemoveLastN( tbl[Balance], 1),
out = Table.FromColumns( Table.ToColumns(tbl) & {prior_month}, Table.ColumnNames(tbl) & {"Prior Month"})] [out],
gr = Table.Group(Source, {"Entity", "Account Number"}, {{"all", each f(_)}}),
expand = Table.ExpandTableColumn(gr, "all", {"Date", "Balance", "Prior Month"}, {"Date", "Balance", "Prior Month"})
in
expand
An issue most likely due to embedding your code into the pre-existing code, or not articulating the data structure. Here is an excerpt of 2 Acct #s:
1) Note the data starts with 3/31/22
2) There may be an issue if the prior month is in a prior year
3 ) Some of the Prior Month results are seemingly correct (highlighted in red) while other months are not
| Entity | Account Number | Description | Date | Balance | Prior Month |
| FVE | 4000-00-00 | SALES 1 | 3/31/2022 | ($2,330.08) | ($5,972.62) |
| FVE | 4000-00-00 | SALES 1 | 4/30/2022 | ($4,890.36) | ($2,330.08) |
| FVE | 4000-00-00 | SALES 1 | 5/31/2022 | ($5,972.62) | ($13,285.10) |
| FVE | 4000-00-00 | SALES 1 | 6/30/2022 | ($13,285.10) | ($14,388.49) |
| FVE | 4000-00-00 | SALES 1 | 7/31/2022 | ($14,388.49) | ($20,713) |
| FVE | 4000-00-00 | SALES 1 | 8/31/2022 | ($20,713.00) | ($32,338.88) |
| FVE | 4000-00-00 | SALES 1 | 9/30/2022 | ($32,338.88) | ($31,558.75) |
| FVE | 4000-00-00 | SALES 1 | 10/31/2022 | ($31,558.75) | ($47,740.27) |
| FVE | 4000-00-00 | SALES 1 | 11/30/2022 | ($47,740.27) | ($57,606.09) |
| FVE | 4000-00-00 | SALES 1 | 12/31/2022 | ($49,812.30) | ($35,934.17) |
| FVE | 4000-00-00 | SALES 1 | 1/31/2023 | ($2,910.00) | $0 |
| FVE | 4000-00-00 | SALES 1 | 2/28/2023 | ($15,823.60) | ($2,910) |
| FVE | 4000-00-00 | SALES 1 | 3/31/2023 | ($25,206.91) | ($15,823.60) |
| FVE | 4000-00-00 | SALES 1 | 4/30/2023 | ($35,934.17) | ($25,206.91) |
| FVE | 4000-00-00 | SALES 1 | 5/31/2023 | ($57,606.09) | ($49,812.30) |
| WESS | 4000 | SALES 3 | 3/31/2022 | ($2,275.00) | ($265,195.40) |
| WESS | 4000 | SALES 3 | 4/30/2022 | ($127,130.40) | ($2,275) |
| WESS | 4000 | SALES 3 | 5/31/2022 | ($265,195.40) | ($448,113.75) |
| WESS | 4000 | SALES 3 | 6/30/2022 | ($448,113.75) | ($518,720.40) |
| WESS | 4000 | SALES 3 | 7/31/2022 | ($518,720.40) | ($748,675.40) |
| WESS | 4000 | SALES 3 | 8/31/2022 | ($748,675.40) | ($938,658.77) |
| WESS | 4000 | SALES 3 | 9/30/2022 | ($938,658.77) | ($1,090,123.77) |
| WESS | 4000 | SALES 3 | 10/31/2022 | ($1,090,123.77) | ($1,253,208.77) |
| WESS | 4000 | SALES 3 | 11/30/2022 | ($1,253,208.77) | ($1,476,318.77) |
| WESS | 4000 | SALES 3 | 12/31/2022 | ($1,476,318.77) | ($783,390) |
| WESS | 4000 | SALES 3 | 1/31/2023 | ($183,245.00) | $0 |
| WESS | 4000 | SALES 3 | 2/28/2023 | ($283,535.00) | ($183,245) |
| WESS | 4000 | SALES 3 | 3/31/2023 | ($360,890.00) | ($283,535) |
| WESS | 4000 | SALES 3 | 4/30/2023 | ($554,170.00) | ($360,890) |
| WESS | 4000 | SALES 3 | 5/31/2023 | ($783,390.00) | ($554,170) |
Sorry, my bad Alien Sx ... your solution is working when filtering the table in Power Query... I'm doing something wrong in the visualizations ... will advise
JRParker it should work. I just grouped by 3 columns (Entity, Account Number and Description) and worked just fine
Sorry, such a nubie at this... so after and outside the query, I need to group the data as your stated in your original post:
"group your data by Entity and Account Number and then do whatever you want to each group of data. If you have 1 balance per month per every Entity & Account then try this"
I need to explore grouping in Data View and/or Report View.