Solved: Re: Adding Custom Column To Obtain Prior Month Bal... - Page 2

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

JRParker · ‎07-08-2023

AlienSx, I don't understand where, when, or how you're grouping to get the results.

This is the Power Query with your code embedded; presumably I did this correctly:

let
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")),
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(#"Filtered Rows", {"Entity", "Account Number", "Description", "Account # - Description" }, {{"all", each f(_)}}),
expand = Table.ExpandTableColumn(gr, "all", {"Date", "Balance", "Prior Month"}, {"Date", "Balance", "Prior Month"}),
#"Changed Type" = Table.TransformColumnTypes(expand,{{"Balance", Currency.Type}, {"Prior Month", Currency.Type}, {"Entity", type text}, {"Date", type date}})
in
#"Changed Type"

Below is the default output after the query. Note the prior month values equate to the previous row values , but the previous row isn't necessarily the previous month:

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)

This is what it looks like after sorting by Date:

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)

Apparently, one needs to due additional grouping (other than the original grouping in the code you provided [gr = Table.Group(#"Filtered Rows", {"Entity", "Account Number", "Description", "Account # - Description" }, {{"all", each f(_)}}),) ].

After the Query steps above, still in PQ, I performed a Group By (Advanced) Entity, Account Number, and Description, (New Column Name Entity-Acct, Operation All Rows). The result was the New Column of Tables, which I expanded to include the remaining columns... the results are the same as noted above.

Appreciate your patience but there is something I'm not getting.

AlienSx · ‎07-08-2023

@JRParker lets just do it straight. What is your financial year? Jan through Dec ?

JRParker · ‎07-08-2023

correct

AlienSx · ‎07-08-2023

@JRParker then replace gr step with the following 2 steps

    add_FY = Table.AddColumn(#"Filtered Rows", "FY", (x) => Date.Year(x[Date])),
    gr = 
        Table.Group(
            add_FY, { "Entity", "Account Number", "Description", "Account # - Description", "FY" }, 
            {{"all", each f(_)}}
        )

JRParker · ‎07-09-2023

So after running the query and filtering to just one account, the default view in Power Query editor:

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

Here is the result in Report View with the default sorted by Date:

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

Don't understand the code enough to understand how the results end up as they do.

here is the code along with notes provided by ChatGPT prior to my filtering to one account:

let
// Step 1: Define the data source as the "Trial Balance" table
Source = #"Trial Balance",

// Step 2: Merge the "Trial Balance" table with the "Account Category" table based on the "Account Number" column
#"Merged Queries" = Table.NestedJoin(Source, {"Account Number"}, #"Account Category", {"Account Number"}, "Account Category", JoinKind.LeftOuter),

// Step 3: Expand the "Account Category" column to include the "Statement" column
#"Expanded Account Category" = Table.ExpandTableColumn(#"Merged Queries", "Account Category", {"Statement"}, {"Account Category.Statement"}),

// Step 4: Filter the rows to include only those with a "Statement" value of "Income Statement"
#"Filtered Rows" = Table.SelectRows(#"Expanded Account Category", each ([Account Category.Statement] = "Income Statement")),

// Step 5: Define a function "f" to perform further operations on each grouped table
f = (tbl as table) as table =>
[
sorted = Table.Sort(tbl, "Date"), // Sort the table by the "Date" column
prior_month = {0} & List.RemoveLastN(tbl[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(tbl) & {prior_month}, Table.ColumnNames(tbl) & {"Prior Month"}) // Add the prior month balances as a new column named "Prior Month"
] [out],

// Step 6: Add a custom column "FY" to extract the fiscal year from the "Date" column
add_FY = Table.AddColumn(#"Filtered Rows", "FY", (x) => Date.Year(x[Date])),

// Step 7: Group the data by specific columns and apply the function "f" to each group
gr = Table.Group(add_FY, { "Entity", "Account Number", "Description", "Account # - Description", "FY" }, {{"all", each f(_)}}),

// Step 8: Expand the "all" column to include the "Date", "Balance", and "Prior Month" columns
expand = Table.ExpandTableColumn(gr, "all", {"Date", "Balance", "Prior Month"}, {"Date", "Balance", "Prior Month"}),

// Step 9: Change the data types of columns to the appropriate types
#"Changed Type" = Table.TransformColumnTypes(expand, {{"Balance", Currency.Type}, {"Prior Month", Currency.Type}, {"Entity", type text}, {"Date", type date}})
in
// Step 10: Return the final transformed table
#"Changed Type"

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]

JRParker · ‎07-09-2023

Yeah baby! .... Bingo ..... thank you, thank you, thank you AlienSx!!! .....here is the result from Report View sorting by Date, including an added column Current Activity (the difference be between Balance and Prior month in the case of any month other than January; else Current Activity is Balance):

Entity	Account Number	FY	Date	Balance	Prior Month	Current Activity
FVE	4000-00-00	2022	3/31/2022	($2,330.08)	$0.00	($2,330.08)
FVE	4000-00-00	2022	4/30/2022	($4,890.36)	($2,330.08)	($2,560.28)
FVE	4000-00-00	2022	5/31/2022	($5,972.62)	($4,890.36)	($1,082.26)
FVE	4000-00-00	2022	6/30/2022	($13,285.10)	($5,972.62)	($7,312.48)
FVE	4000-00-00	2022	7/31/2022	($14,388.49)	($13,285.10)	($1,103.39)
FVE	4000-00-00	2022	8/31/2022	($20,713.00)	($14,388.49)	($6,324.51)
FVE	4000-00-00	2022	9/30/2022	($32,338.88)	($20,713.00)	($11,625.88)
FVE	4000-00-00	2022	10/31/2022	($31,558.75)	($32,338.88)	$780.13
FVE	4000-00-00	2022	11/30/2022	($47,740.27)	($31,558.75)	($16,181.52)
FVE	4000-00-00	2022	12/31/2022	($49,812.30)	($47,740.27)	($2,072.03)
FVE	4000-00-00	2023	1/31/2023	($2,910.00)	$0.00	($2,910.00)
FVE	4000-00-00	2023	2/28/2023	($15,823.60)	($2,910.00)	($12,913.60)
FVE	4000-00-00	2023	3/31/2023	($25,206.91)	($15,823.60)	($9,383.31)
FVE	4000-00-00	2023	4/30/2023	($35,934.17)	($25,206.91)	($10,727.26)
FVE	4000-00-00	2023	5/31/2023	($57,606.09)	($35,934.17)	($21,671.92)

For the record, here is the code:

let
Source = #"Trial Balance",

// Step 1: Merge the "Trial Balance" table with the "Account Category" table based on the "Account Number" column
#"Merged Queries" = Table.NestedJoin(Source, {"Account Number"}, #"Account Category", {"Account Number"}, "Account Category", JoinKind.LeftOuter),

// Step 2: Expand the "Account Category" column to include the "Statement" column
#"Expanded Account Category" = Table.ExpandTableColumn(#"Merged Queries", "Account Category", {"Statement"}, {"Account Category.Statement"}),

// Step 3: Filter the rows to include only those with a "Statement" value of "Income Statement"
#"Filtered Rows" = Table.SelectRows(#"Expanded Account Category", each ([Account Category.Statement] = "Income Statement")),

// Step 4: Define a function "f" to perform further operations on each grouped table
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],

// Step 5: Add a custom column "FY" to extract the fiscal year from the "Date" column
add_FY = Table.AddColumn(#"Filtered Rows", "FY", (x) => Date.Year(x[Date])),

// Step 6: Group the data by specific columns and apply the function "f" to each group
gr = Table.Group(add_FY, { "Entity", "Account Number", "Description", "Account # - Description", "FY" }, {{"all", each f(_)}}),

// Step 7: Expand the "all" column to include the "Date", "Balance", and "Prior Month" columns
expand = Table.ExpandTableColumn(gr, "all", {"Date", "Balance", "Prior Month"}, {"Date", "Balance", "Prior Month"}),

// Step 8: Change the data types of columns to the appropriate types
#"Changed Type" = Table.TransformColumnTypes(expand, {{"Balance", Currency.Type}, {"Prior Month", Currency.Type}, {"Entity", type text}, {"Date", type date}}),

// Step 9: Add a custom column "Current Activity" to calculate the current month's activity based on the conditions
#"Added Custom" = Table.AddColumn(#"Changed Type", "Current Activity", each if Date.Month([Date]) = 1 then [Balance] else [Balance] - [Prior Month]),

// Step 10: Change the data type of the "Current Activity" column to Currency
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"Current Activity", Currency.Type}})
in
#"Changed Type1"

JRParker · ‎07-07-2023

Almost forgot the bigger objective. Determining the Prior Month was really the first of two steps in my effort to ultimately calculate the "Current Month Activity". In accounting, the Income Statement (not the Balance Sheet) Trial Balance accounts are a cumlative balance starting at the beginning of the fiscal year (in this case the calendar year where January is month 1), and resets at the end of the year. So, with the exception of month 1, the difference between the Balance and Prior Month would be the Current Month Activity. In the case of month 1, the Balance is the Current Month Activity. For example, Balance in month 1 is 1000, Balance in month 2 is 1600, then the Currnet Month Activity for month 1 and month 2 would be 1000 and 600, respectively.

With the Prior Month determined, the logic would be:

IF month =1 THEN Current Month Activity = Balance ELSE Current Month Activity = Balance - Prior Month.

After Current Month Activity was determined, then the Prior Month column was going to be remvoved as no longer needed.

I'm not sure how to do this, particuarly in light of the grouping that needs to be done to get it right. Can you advise?

AlienSx · ‎07-08-2023

@JRParker I would add custom column "FY" with fiscal year calculation and then add this column as group by parameter along with Entity and Account Number.

JRParker · ‎07-07-2023

Sorry, such a nubie at this... so after and outside the query, I need to group the data as your stated in your original post:

"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"

I need to explore grouping in Data View and/or Report View.

JRParker · ‎07-07-2023

Wicked! AlienSx = 1 ChatGPT = 0

One issue that surfaces presumably having to do with the original source of my table. First, below is a summary of the Power Query code with your code embedded and an explanation of each line by ChatGPT. Note that prior to your code commencing at Step 5, we brought in the Trial Balance table, merged a column named Statement, and filtered the Statement column to only "Income Statement" accounts (vs "Balance Sheet" accounts). Interestingly, the final results of this query include both "Income Statement" and "Balance Sheet" accounts where only "Income Statement" accounts should result. Can you advise how to resolve this?

let
// Step 1: Define the data source as the "Trial Balance" table
Source = #"Trial Balance",
// Step 2: Merge the "Trial Balance" table with the "Account Category" table based on the "Account Number" column
#"Merged Queries" = Table.NestedJoin(Source, {"Account Number"}, #"Account Category", {"Account Number"}, "Account Category", JoinKind.LeftOuter),
// Step 3: Expand the "Account Category" column to include the "Statement" column
#"Expanded Account Category" = Table.ExpandTableColumn(#"Merged Queries", "Account Category", {"Statement"}, {"Account Category.Statement"}),
// Step 4: Filter the rows to include only those with a "Statement" value of "Income Statement"
#"Filtered Rows" = Table.SelectRows(#"Expanded Account Category", each ([Account Category.Statement] = "Income Statement")),
// Step 5: Define a function "f" to perform further operations on each grouped 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],
// Step 6: Group the data by specific columns and apply the function "f" to each group
gr = Table.Group(Source, {"Entity", "Account Number", "Description", "Account # - Description" }, {{"all", each f(_)}}),
// Step 7: Expand the "all" column to include the "Date", "Balance", and "Prior Month" columns
expand = Table.ExpandTableColumn(gr, "all", {"Date", "Balance", "Prior Month"}, {"Date", "Balance", "Prior Month"}),
// Step 8: Change the data types of the "Balance" and "Prior Month" columns to Currency
#"Changed Type" = Table.TransformColumnTypes(expand,{{"Balance", Currency.Type}, {"Prior Month", Currency.Type}})
in
// Step 9: Return the final transformed table
#"Changed Type"

AlienSx · ‎07-07-2023

@JRParker in step 6: replace Source by #"Filtered Rows"

JRParker · ‎07-07-2023

got it... done... thank you! .... see prior post regarding an outstanding issue.

JRParker · ‎07-07-2023

Just saw this post AlienSx ... let me experiment and advise... thanks... Jim

Greg_Deckler · ‎07-06-2023

@JRParker Here is one way of doing it. However, for additional Entities and Account Numbers you may need to modify things a bit but perhaps not.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZLJjcMwDEV7MXIYAgTNRQtVQKaEuQTuv40oiDGxSOQkCfB7X/7i47H9/t033Aozz8V2k11Zde5/bopmTOwwT7e54e3AAJTd+AMU9MFkDQKesLrkVBxdqSkEScLakiaG6pWEIVgS15c4KWjuVAZETQJ97YOxi80eIHoSOJab2qsKJ3eIngQKL5EmWKtTrxBFmZT1LTr2wqQdoiiTumSWgS5Kdv7mRZTJE7TzwYfwfz0XTeJ0V/9wUtHVqDEETeJszauo3GgIRM3XOX2DNmfFCsnZzcXzdVLfYO3Y5pd8js7FcxxP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entity = _t, #"Account Number" = _t, Date = _t, Balance = _t, #"Prior Month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity", type text}, {"Account Number", Int64.Type}, {"Date", type date}, {"Balance", Currency.Type}, {"Prior Month", Currency.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Prior Month"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Balance"}, {"Added Index1.Balance"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Added Index1",null,0,Replacer.ReplaceValue,{"Added Index1.Balance"})
in
    #"Replaced Value"

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

Greg_Deckler · ‎07-06-2023

@JRParker See if this helps, this video covers all of the common time intelligence scenarios.

The PBIX is available here: MicrosoftHatesGregsQuickMeasures/PBIX at main · gdeckler/MicrosoftHatesGregsQuickMeasures (github.co...

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

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

Thanks Greg... replied to my original post, but will reply directly to your post.

....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)

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