Forum Discussion

JRParker's avatar
JRParker
Helper III
3 years ago
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:...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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's avatar
JRParker
Helper III
3 years ago

I think you nailed it in your comment: "However, for additional Entities and Account Numbers you may need to modify things a bit but perhaps not."   Don't think I provided you adequate data sample and context at first. 

 

If I follow your code with two index columns and then merging the two indexes, that works fine if there was only one account number; just simply offset the index values by one to get the prior month.  But as you can see in the more elaborate data provided here, this doesn't work.   I would love to send you a sample table if I could figure out how to send an attachment.

 

Don't see how to add an attachment of sample data, but here is an excerpt for context:

 

Entity    Account               Date                   Balance              Prior Month

Entity 2 4000                     3/31/2022          ($2,275.00)        ($65.86)

Entity 1 4000-00-00          3/31/2022          ($2,330.08)        $6,250.00

Entity 1 4000-00-10          3/31/2022          ($32,257.70)      ($2,330.08)

Entity 1 4000-00-50          3/31/2022          ($58,840.50)      ($32,257.70)

Entity 1 4000-00-70          3/31/2022          ($121,970.09)   ($58,840.50)

Entity 2 4005                     3/31/2022          ($69,298.66)      ($2,275.00)

Entity 2 4010                     3/31/2022          $140.00               ($69,298.66)

Entity 1 4010-00-00          3/31/2022          ($46,717.71)      ($121,970.09)

Entity 1 4010-00-50          3/31/2022          ($67,874.32)      ($46,717.71)

Entity 1 4100-00-00          3/31/2022          ($13,963.11)      ($67,874.32)

Entity 1 4200-00-00          3/31/2022          $21,426.84         ($13,963.11)

Entity 1 4400-00-00          3/31/2022          $122.47               $21,426.84

INTER    4999                    3/31/2022          ($18,720.29)      ($1,500.00)

Entity 2 5000                     3/31/2022          $1,696.09           $140.00

Entity 1 5000-00-00          3/31/2022          $17,493.21         $122.47

Entity 1 5000-00-10          3/31/2022          $11,027.87         $17,493.21

Entity 1 5000-00-50          3/31/2022          $77,614.45         $11,027.87