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]
Almost forgot the bigger objective. Determining the Prior Month was really the first of two steps in my effort to ultimately calculate the "Current Month Activity". In accounting, the Income Statement (not the Balance Sheet) Trial Balance accounts are a cumlative balance starting at the beginning of the fiscal year (in this case the calendar year where January is month 1), and resets at the end of the year. So, with the exception of month 1, the difference between the Balance and Prior Month would be the Current Month Activity. In the case of month 1, the Balance is the Current Month Activity. For example, Balance in month 1 is 1000, Balance in month 2 is 1600, then the Currnet Month Activity for month 1 and month 2 would be 1000 and 600, respectively.
With the Prior Month determined, the logic would be:
IF month =1 THEN Current Month Activity = Balance ELSE Current Month Activity = Balance - Prior Month.
After Current Month Activity was determined, then the Prior Month column was going to be remvoved as no longer needed.
I'm not sure how to do this, particuarly in light of the grouping that needs to be done to get it right. Can you advise?