Forum Discussion
Solved
Adding Custom Column To Obtain Prior Month Balance
Trying to create a custom column 'Prior Month Balance' by looking at the [Balance] column of the prior month with the same Entity and Account Number. Here are the relevant columns of the table:...
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]
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.