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.

  • JRParker's avatar
    JRParker
    Helper III
    3 years ago

    Here is the equivalent DAX measure without relying on a related DATE table:  

     

    Prior Month Activity =
    VAR CurrentDate = SELECTEDVALUE('Income Statement Data'[Date])
    VAR CurrentMonthNumber = MONTH(CurrentDate)
    VAR CurrentYear = YEAR(CurrentDate)
    VAR CurrentActuals = [Actuals IS YTD]

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

    VAR PriorMonthBalance =
        CALCULATE(
            [Actuals IS YTD],
            FILTER(
                ALL('Income Statement Data'),
                YEAR('Income Statement Data'[Date]) = PriorMonthYear &&
                MONTH('Income Statement Data'[Date]) = PriorMonthNumber &&
                NOT(ISBLANK([Actuals IS YTD]))
            )
        )

    RETURN
    IF(ISBLANK(CurrentActuals),
        BLANK(),
        PriorMonthBalance
    )
     
    Asked ChatGPT what the equivalent of the DAX Measure would be with a Power Query formula and it could not solve; persistent "Expression.Error: A cyclic reference was encountered during evaluation."  One would think if a DAX measure can solve, a Power Query could as well.
    • JRParker's avatar
      JRParker
      Helper III
      3 years ago

      The [Actuals IS YTD] is another Measure which simply uses the SUM function to sum the [Balance].  So one would think [Actuals IS YTD] could simply be replaced with [Balance], but perhaps that is the crux of the prolem attempting this in Power Query?