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]
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.
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?