Solved: Adding Custom Column To Obtain Prior Month Balance

JRParker · ‎07-06-2023

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:

Entity Type Text

Account Number Type Text

Date Type Date

Balance Type Fixed Decimal

Managed to create a Measure, but want to have this in the table from the outset.

I'll spare you the details of various attempts with circular references, etc..., even spent a day with ChatGPT to no avail. :).

Thank you for any insight you may provide. Jim

AlienSx · ‎07-09-2023

@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]

View solution in original post

Duh · ‎07-07-2023

Hi Dont want to sidetrack what you are doing but the below video is very similar to what you want to achieve.

https://www.youtube.com/watch?v=IGF2-qfzDQs&t=775s

JRParker · ‎07-07-2023

Thank you Duh .... very insightful... I have a long way to go in learning the M language. In the interim, I'm not qualified to solve this issue as noted in message 16 in this thread.

JRParker · ‎07-06-2023

Oh, and I'm using Power BI Desktop.

Greg_Deckler · ‎07-06-2023

@JRParker Here is the PBIX file (attached below signature).

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

JRParker · ‎07-06-2023

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 · ‎07-06-2023

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 · ‎07-06-2023

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 · ‎07-06-2023

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?

JRParker · ‎07-06-2023

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

JRParker · ‎07-06-2023

Greg, thanks for providing the pbix file; very helpful. While your pbix code is right on with the correct results, I've added what I believe to be the relevant lines to the query of my existing table and have incorrect results. Can you advise what I've done wrong? Note there are two Merged Queries:

let

// note this table is first a reference to a Trial Balance table

Source = #"Trial Balance",
#"Merged Queries" = Table.NestedJoin(Source, {"Account Number"}, #"Account Category", {"Account Number"}, "Account Category", JoinKind.LeftOuter),
#"Expanded Account Category" = Table.ExpandTableColumn(#"Merged Queries", "Account Category", {"Statement"}, {"Account Category.Statement"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Account Category", each ([Account Category.Statement] = "Income Statement")),

// this is the point where we have the existing table and want to add the custom column

// added these steps from your code (along with the ',' at the end of the previous line

#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
#"Merged Queries-2" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries-2", "Added Index1", {"Balance"}, {"Added Index1.Balance"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Added Index1",null,0,Replacer.ReplaceValue,{"Added Index1.Balance"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Date", Order.Ascending}}),

// added these over and above your code to remove unncessary columns and rename the custom column

#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Account Category.Statement", "Index", "Index.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Added Index1.Balance", "Prior Month Balance"}})
in
#"Renamed Columns"

Let me know if you need to know what kind of results other than incorrect results.

JRParker · ‎07-06-2023

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

JRParker · ‎07-06-2023

Don't know how to add an attachment, but here is an excerpt of the table, including the custom column Prior Month Balance:

Entity Account Date Balance Prior Month Balance
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
Entity 1 5000-00-70 3/31/2022 $84,398.13 $77,614.45
Entity 1 5000-00-90 3/31/2022 ($4,883.34) $84,398.13
Entity 2 5010 3/31/2022 $20.00 $1,696.09
Entity 2 5200 3/31/2022 $8,440.00 $424.50
Entity 2 5250 3/31/2022 $316.50 $8,440.00
Entity 1 5300-00-00 3/31/2022 ($17,385.99) ($4,883.34)
Entity 2 5305 3/31/2022 $424.50 $20.00
Entity 2 5315 3/31/2022 $360.00 $316.50
Entity 1 5320-00-00 3/31/2022 ($1,739.56) ($17,385.99)
Entity 1 5400-00-00 3/31/2022 $32,684.58 ($1,739.56)
Entity 1 5400-00-10 3/31/2022 $5,663.44 $32,684.58
Entity 1 5400-00-20 3/31/2022 $3,986.02 $5,663.44
Entity 1 5400-00-30 3/31/2022 $3,048.88 $3,986.02
Entity 1 5400-00-40 3/31/2022 $3,534.82 $3,048.88

JRParker · ‎07-06-2023

Pretty sure it is not relevant in that it they are not referenced in your code, but the Entity and Account Number columns are different Data Types Any any Text, respectively, while your code has Text and whole number, respectively.

JRParker · ‎07-06-2023

BTW, the intent is create the custom column in Power Query; not Data View. Here is sample data; all but the last column is sample data from the table, with the last column the custom column with the expected results:

Entity	Account Number	Date	Balance	Prior Month
FVE	4000	3/31/2022	($2,330.08)	$0.00
FVE	4000	4/30/2022	($4,890.36)	($2,330.08)
FVE	4000	5/31/2022	($5,972.62)	($4,890.36)
FVE	4000	6/30/2022	($13,285.10)	($5,972.62)
FVE	4000	7/31/2022	($14,388.49)	($13,285.10)
FVE	4000	8/31/2022	($20,713.00)	($14,388.49)
FVE	4000	9/30/2022	($32,338.88)	($20,713.00)
FVE	4000	10/31/2022	($31,558.75)	($32,338.88)
FVE	4000	11/30/2022	($47,740.27)	($31,558.75)
FVE	4000	12/31/2022	($49,812.30)	($47,740.27)
FVE	4000	1/31/2023	($2,910.00)	($49,812.30)
FVE	4000	2/28/2023	($15,823.60)	($2,910.00)
FVE	4000	3/31/2023	($25,206.91)	($15,823.60)
FVE	4000	4/30/2023	($35,934.17)	($25,206.91)
FVE	4000	5/31/2023	($57,606.09)	($35,934.17)

JRParker · ‎07-06-2023

Know there are other Entities and Account Numbers in the table. 🙂

AlienSx · ‎07-06-2023

hi, @JRParker group your data by Entity and Account Number and then do whatever you want to each group of data. If you have 1 balance per month per every Entity & Account then try this

let
    Source = your_table,
    f = (tbl as table) as table =>
        [sorted = Table.Sort(tbl, "Date"),
        prior_month = {0} & List.RemoveLastN( tbl[Balance], 1),
        out = Table.FromColumns( Table.ToColumns(tbl) & {prior_month}, Table.ColumnNames(tbl) & {"Prior Month"})] [out],
    gr = Table.Group(Source, {"Entity", "Account Number"}, {{"all", each f(_)}}),
    expand = Table.ExpandTableColumn(gr, "all", {"Date", "Balance", "Prior Month"}, {"Date", "Balance", "Prior Month"})
in
    expand

JRParker · ‎07-07-2023

Ok, there is an issue that likely has something to do with the sorting or timing thereof in Power Query. Here is a snapshot of the table after the query is run; note the default date sequence and the Prior Month values relative to the prior row (?) and it gets out of whack at the change of the year. Then note what happens after sorting on the Date column:

Entity	Account Number	Date	Balance	Prior Month
FVE	4000-00-00	1/31/2023	$ (2,910.00)	$ -
FVE	4000-00-00	2/28/2023	$ (15,823.60)	$ (2,910.00)
FVE	4000-00-00	3/31/2023	$ (25,206.91)	$ (15,823.60)
FVE	4000-00-00	4/30/2023	$ (35,934.17)	$ (25,206.91)
FVE	4000-00-00	12/31/2022	$ (49,812.30)	$ (35,934.17)
FVE	4000-00-00	5/31/2023	$ (57,606.09)	$ (49,812.30)
FVE	4000-00-00	11/30/2022	$ (47,740.27)	$ (57,606.09)
FVE	4000-00-00	10/31/2022	$ (31,558.75)	$ (47,740.27)
FVE	4000-00-00	9/30/2022	$ (32,338.88)	$ (31,558.75)
FVE	4000-00-00	8/31/2022	$ (20,713.00)	$ (32,338.88)
FVE	4000-00-00	7/31/2022	$ (14,388.49)	$ (20,713.00)
FVE	4000-00-00	6/30/2022	$ (13,285.10)	$ (14,388.49)
FVE	4000-00-00	5/31/2022	$ (5,972.62)	$ (13,285.10)
FVE	4000-00-00	3/31/2022	$ (2,330.08)	$ (5,972.62)
FVE	4000-00-00	4/30/2022	$ (4,890.36)	$ (2,330.08)

Entity	Account Number	Date	Balance	Prior Month
FVE	4000-00-00	3/31/2022	$ (2,330.08)	$ (5,972.62)
FVE	4000-00-00	4/30/2022	$ (4,890.36)	$ (2,330.08)
FVE	4000-00-00	5/31/2022	$ (5,972.62)	$ (13,285.10)
FVE	4000-00-00	6/30/2022	$ (13,285.10)	$ (14,388.49)
FVE	4000-00-00	7/31/2022	$ (14,388.49)	$ (20,713.00)
FVE	4000-00-00	8/31/2022	$ (20,713.00)	$ (32,338.88)
FVE	4000-00-00	9/30/2022	$ (32,338.88)	$ (31,558.75)
FVE	4000-00-00	10/31/2022	$ (31,558.75)	$ (47,740.27)
FVE	4000-00-00	11/30/2022	$ (47,740.27)	$ (57,606.09)
FVE	4000-00-00	12/31/2022	$ (49,812.30)	$ (35,934.17)
FVE	4000-00-00	1/31/2023	$ (2,910.00)	$ -
FVE	4000-00-00	2/28/2023	$ (15,823.60)	$ (2,910.00)
FVE	4000-00-00	3/31/2023	$ (25,206.91)	$ (15,823.60)
FVE	4000-00-00	4/30/2023	$ (35,934.17)	$ (25,206.91)
FVE	4000-00-00	5/31/2023	$ (57,606.09)	$ (49,812.30)

JRParker · ‎07-07-2023

An issue most likely due to embedding your code into the pre-existing code, or not articulating the data structure. Here is an excerpt of 2 Acct #s:

1) Note the data starts with 3/31/22

2) There may be an issue if the prior month is in a prior year

3 ) Some of the Prior Month results are seemingly correct (highlighted in red) while other months are not

Entity	Account Number	Description	Date	Balance	Prior Month
FVE	4000-00-00	SALES 1	3/31/2022	($2,330.08)	($5,972.62)
FVE	4000-00-00	SALES 1	4/30/2022	($4,890.36)	($2,330.08)
FVE	4000-00-00	SALES 1	5/31/2022	($5,972.62)	($13,285.10)
FVE	4000-00-00	SALES 1	6/30/2022	($13,285.10)	($14,388.49)
FVE	4000-00-00	SALES 1	7/31/2022	($14,388.49)	($20,713)
FVE	4000-00-00	SALES 1	8/31/2022	($20,713.00)	($32,338.88)
FVE	4000-00-00	SALES 1	9/30/2022	($32,338.88)	($31,558.75)
FVE	4000-00-00	SALES 1	10/31/2022	($31,558.75)	($47,740.27)
FVE	4000-00-00	SALES 1	11/30/2022	($47,740.27)	($57,606.09)
FVE	4000-00-00	SALES 1	12/31/2022	($49,812.30)	($35,934.17)
FVE	4000-00-00	SALES 1	1/31/2023	($2,910.00)	$0
FVE	4000-00-00	SALES 1	2/28/2023	($15,823.60)	($2,910)
FVE	4000-00-00	SALES 1	3/31/2023	($25,206.91)	($15,823.60)
FVE	4000-00-00	SALES 1	4/30/2023	($35,934.17)	($25,206.91)
FVE	4000-00-00	SALES 1	5/31/2023	($57,606.09)	($49,812.30)
WESS	4000	SALES 3	3/31/2022	($2,275.00)	($265,195.40)
WESS	4000	SALES 3	4/30/2022	($127,130.40)	($2,275)
WESS	4000	SALES 3	5/31/2022	($265,195.40)	($448,113.75)
WESS	4000	SALES 3	6/30/2022	($448,113.75)	($518,720.40)
WESS	4000	SALES 3	7/31/2022	($518,720.40)	($748,675.40)
WESS	4000	SALES 3	8/31/2022	($748,675.40)	($938,658.77)
WESS	4000	SALES 3	9/30/2022	($938,658.77)	($1,090,123.77)
WESS	4000	SALES 3	10/31/2022	($1,090,123.77)	($1,253,208.77)
WESS	4000	SALES 3	11/30/2022	($1,253,208.77)	($1,476,318.77)
WESS	4000	SALES 3	12/31/2022	($1,476,318.77)	($783,390)
WESS	4000	SALES 3	1/31/2023	($183,245.00)	$0
WESS	4000	SALES 3	2/28/2023	($283,535.00)	($183,245)
WESS	4000	SALES 3	3/31/2023	($360,890.00)	($283,535)
WESS	4000	SALES 3	4/30/2023	($554,170.00)	($360,890)
WESS	4000	SALES 3	5/31/2023	($783,390.00)	($554,170)

JRParker · ‎07-07-2023

Sorry, my bad Alien Sx ... your solution is working when filtering the table in Power Query... I'm doing something wrong in the visualizations ... will advise

AlienSx · ‎07-07-2023

@JRParker it should work. I just grouped by 3 columns (Entity, Account Number and Description) and worked just fine

Adding Custom Column To Obtain Prior Month Balance

Helpful resources

Power BI Monthly Update - September 2025

Fabric Community Update - August 2025

FabCon is coming to Atlanta

Adding Custom Column To Obtain Prior Month Balance

Helpful resources

Power BI Monthly Update - September 2025

Fabric Community Update - August 2025