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

This is what I was able to do with a Measure (with help from ChatGPT), but resources are exceeded in visualizations (I'll spare you the details as to why).  So the idea was to create this in Power Query as a custom column.  Here is the DAX Measure:

 

Prior Month Activity =

VAR CurrentMonthNumber = SELECTEDVALUE('Date'[Month of Year])

VAR CurrentYear = SELECTEDVALUE('Date'[Year])

VAR CurrentBalance = [Balance]

 

VAR PriorMonthNumber = IF(CurrentMonthNumber = 1, 12, CurrentMonthNumber - 1)

VAR PriorMonthYear =

    IF(PriorMonthNumber = 12, CurrentYear - 1, CurrentYear)

 

VAR PriorMonthBalance =

    CALCULATE(

        [Balance],

        FILTER(

            ALL('Date'),

            'Date'[Year] = PriorMonthYear &&

            'Date'[Month of Year] = PriorMonthNumber &&

            NOT(ISBLANK([Balance]))

        )

    )

 

RETURN

IF( ISBLANK(CurrentBalance),

    BLANK(),

    PriorMonthBalance

    )

JRParker's avatar
JRParker
Helper III
3 years ago

I should have mentioned that there is a related DATE table and is referenced in this Measure; one would have to rely on the DATE column in the table in the case of Power Query.