Custom Column to calculate account ending balance

[JRParker]

>Add column calculating ending Balance for each Account for each Period, for each Entity for Statement type BS in the TB table
>Statement type IS, the Balance is simply Debits - Credits; no prior Balance to carryforward
In the case of Statement BS, the Balance is the the prior period (month) Balance plus the Debits - Credits for each Period.
>The TB table has various Periods (YYYYMM) of 2 Statement types of various Entities
>For BS accounts, commence with an opening Balance at earliest Period, calculating ending Balance going forward
>Fields Text accept Debit and Credit Fixed Decimal
>Presumably, one would create a virtual table of records Grouped by Entity by Account_No and process each set of Entity-Account_No with the different Periods

>Bold red BS Balance values are opening Balances in first Period; bold red ?? is the challange.  IS Balance values calcuated as noted

> Posting sample tabel in separate post due to exceeding character limit.... thanks Jim

[JRParker]

Oh, good point, my sample data should have no debits or credits values in the Opening Period; just assume the Opening Balance values for the BS (Balance Sheet) accounts are from the prior period (i.e., 202005).

Wasn't thinking a DAX formula, but using Power Query add custom column (M language).

[DataNinja777]

Hi @JRParker

In that case, Balance column for the BS is the opening balance and IS is closing balance?  Also, IS closing balance should cumulate during the year and transferred to the retained earnings at the beginning of next year, but the IS balance is just showing the single month balance without accumulating.  Is this OK?

[JRParker]

Sakiko, sorry for not being very clear.   ..here some additional sample data to your original question.  Assume 202005 was the first period with only (some) Balance Sheet accounts (for each entity) with an opening balance:

Entity	Statement	Account_No	Period	Debit	Credit	Balance
ABC	BS	1	202005			5,000
ABC	BS	2	202005			-
ABC	BS	3	202005			1,000
ABC	BS	4	202005			1,000

Regarding the IS accounts, yes it is ok that it is not cumulative; I'll use DAX functions for any YTD, MTD, etc, and handle closing at year end.  

For now, the logic would be IF a BS account then DO THIS, ELSE (for IS accounts) Balance = Debit - Credit.    ... thanks again.

[JRParker]

Entity	Statement	Account_No	Period	Debit	Credit	Balance
ABC	BS	1	202006	1,000	800	5,000
ABC	BS	2	202006	2,500	2,100	-
ABC	BS	3	202006	50	300	1,000
ABC	BS	4	202006	225	125	1,000
ABC	IS	5	202006		1,000	(1,000)
ABC	IS	6	202006		500	(500)
ABC	IS	7	202006		250	(250)
ABC	IS	8	202006	300		300
ABC	IS	9	202006	25		25
ABC	BS	1	202007	1,000	800	??
ABC	BS	2	202007	2,500	2,100	??
ABC	BS	3	202007	50	300	??
ABC	BS	4	202007	225	125	??
ABC	IS	5	202007		1,000	(1,000)
ABC	IS	6	202007		500	(500)
ABC	IS	7	202007		250	(250)
ABC	IS	8	202007	300		300
ABC	IS	9	202007	25		25
XYZ	BS	1	202006	1,000	800	5,000
XYZ	BS	2	202006	2,500	2,100	-
XYZ	BS	3	202006	50	300	1,000
XYZ	BS	4	202006	225	125	1,000
XYZ	IS	5	202006		1,000	(1,000)
XYZ	IS	6	202006		500	(500)
XYZ	IS	7	202006		250	(250)
XYZ	IS	8	202006	300		300
XYZ	IS	9	202006	25		25
XYZ	BS	1	202007	1,000	800	??
XYZ	BS	2	202007	2,500	2,100	??
XYZ	BS	3	202007	50	300	??
XYZ	BS	4	202007	225	125	??
XYZ	IS	5	202007		1000	(1000)
XYZ	IS	6	202007		500	(500)
XYZ	IS	7	202007		250	(250)
XYZ	IS	8	202007	350	50	300
XYZ	IS	9	202007	25		25

[AlienSx]

Hello, @JRParker 

let
    Source = your_table,
    bs = Table.SelectRows(Source, each [Statement] = "BS"),
    f_bs = (tbl as table) as table =>
        [a = List.Buffer(Table.ToRecords(Table.RemoveColumns(tbl, "Balance"))),
        n = List.Count(a),
        b =
            List.Generate(
                () => [i = 0, r = Record.AddField(a{0}, "Balance", try Number.From(tbl{0}[Balance]) otherwise 0)],
                (x) => x[i] < n,
                (x) => [i = x[i] + 1, r = Record.AddField(a{0}, "Balance", x[r][Balance] + a{i}[Debit] - a{i}[Credit])],
                (x) => x[r]
            ),
        z = Table.FromRecords(b)][z],
    gr = Table.Group(bs, {"Entity", "Account_No"}, {{"all", each f_bs(Table.Sort(_, "Period"))}}),
    out = Table.SelectRows(Source, each [Statement] = "IS") & Table.Combine(gr[all])
in
    out

[JRParker]

AlienSx, my apologies for the delayed response... have been anxiously awaiting for the point I could review and test... last ten days has been intense... 

So I embedded your query into my existing query and get the following error:

Expression.Error: The column 'Entity' of the table wasn't found. Details: Entity

at this line:

gr = Table.Group(bs, {"Entity", "Account_No"}, {{"all", each f_bs(Table.Sort(_, "Period"))}}),

Perhaps I embedded incorrectly?  here is my query with your logic at the end...note the merger of various tables, including where the field "Entity" is merged:

let
Source = Folder.Files("\Data Sources\TB XYZ Company"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Account_Key", type text}, {"Account_No", type text}, {"Period", type text}, {"Debit", Currency.Type}, {"Credit", Currency.Type}}),
#"Added Balance" = Table.AddColumn(#"Changed Type1", "Balance", each 0),
#"Changed Balance Type" = Table.TransformColumnTypes(#"Added Balance",{{"Balance", Currency.Type}}),
#"Filtered Blank Rows" = Table.SelectRows(#"Changed Balance Type", each ([Account_Key] <> "")),
#"Duplicated Period" = Table.DuplicateColumn(#"Filtered Blank Rows", "Period", "Period - Copy"),
#"Split Period Components" = Table.SplitColumn(#"Duplicated Period", "Period - Copy", Splitter.SplitTextByPositions({0, 4}, false), {"Period - Copy.1", "Period - Copy.2"}),
#"Renamed Period Components" = Table.RenameColumns(#"Split Period Components",{{"Period - Copy.1", "Year"}, {"Period - Copy.2", "Month"}}),
#"Added Day" = Table.AddColumn(#"Renamed Period Components", "Day", each if [Month] = "01" or [Month] = "03" or [Month] = "05" or [Month] = "07" or [Month] = "08" or [Month] = "10" or [Month] = "12" then 31
else if [Month] = "04" or [Month] = "06" or [Month] = "09" or [Month] = "11" then 30
else if [Month] = "02" and ([Year] = "2020" or [Year] = "2024" or [Year] = "2028") then 29
else 28),
#"Changed Year,Month,Day Type" = Table.TransformColumnTypes(#"Added Day",{{"Account_Key", Int64.Type}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}}),
#"Added Date" = Table.AddColumn(#"Changed Year,Month,Day Type", "Date", each #date([Year],[Month],[Day])),
#"Changed Date Type" = Table.TransformColumnTypes(#"Added Date",{{"Date", type date}}),
#"Removed Date Components" = Table.RemoveColumns(#"Changed Date Type",{"Year", "Month", "Day"}),
#"Added Current Activity" = Table.AddColumn(#"Removed Date Components", "Current Activity", each [Debit] - [Credit]),
#"Changed Current Activity Type" = Table.TransformColumnTypes(#"Added Current Activity",{{"Current Activity", Currency.Type}}),
#"Merged Categories" = Table.NestedJoin(#"Changed Current Activity Type", {"Account_No"}, Category, {"Account No"}, "Category", JoinKind.LeftOuter),
#"Expanded Category" = Table.ExpandTableColumn(#"Merged Categories", "Category", {"Account Name", "Category MAV", "Category # MAV", "Category PLEX", "Category # PLEX", "Sub-Category MAV", "Sub-Cat # MAV", "Sub-Category PLEX", "Sub-Cat # PLEX", "Statement", "Entity"}, {"Account Name", "Category MAV", "Category # MAV", "Category PLEX", "Category # PLEX", "Sub-Category MAV", "Sub-Cat # MAV", "Sub-Category PLEX", "Sub-Cat # PLEX", "Statement", "Entity"}),
#"Merged Base No Name" = Table.NestedJoin(#"Expanded Category", {"Account_No"}, #"Base No Name Query", {"Account_No"}, "Acct_Base_Ref", JoinKind.LeftOuter),
#"Expanded Acct_Base_Ref" = Table.ExpandTableColumn(#"Merged Base No Name", "Acct_Base_Ref", {"Base_No", "Base Name"}, {"Base_No", "Base Name"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Acct_Base_Ref", {"Account_No"}, #"Acct_Cost_Center Query", {"Account_No"}, "Acct_Cost_Center Query", JoinKind.LeftOuter),
#"Expanded Acct_Cost_Center Query" = Table.ExpandTableColumn(#"Merged Queries", "Acct_Cost_Center Query", {"Cost_Center_No", "Cost Center Name"}, {"Cost_Center_No", "Cost Center Name"}),
#"Changed Base No Cost Cntr No Type" = Table.TransformColumnTypes(#"Expanded Acct_Cost_Center Query",{{"Base_No", type text}, {"Cost_Center_No", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Base No Cost Cntr No Type",{"Entity", "Statement", "Period", "Date", "Account_Key", "Account_No", "Account Name", "Base_No", "Base Name", "Cost_Center_No", "Cost Center Name", "Category MAV", "Category # MAV", "Category PLEX", "Category # PLEX", "Sub-Category MAV", "Sub-Cat # MAV", "Sub-Category PLEX", "Sub-Cat # PLEX", "Debit", "Credit", "Current Activity", "Balance"}),
#"Removed Acct Key" = Table.RemoveColumns(#"Reordered Columns",{"Account_Key"}),

//// YOUR QUERY STARTS HERE

bs = Table.SelectRows(Source, each [Statement] = "BS"),
f_bs = (tbl as table) as table =>
[a = List.Buffer(Table.ToRecords(Table.RemoveColumns(tbl, "Balance"))),
n = List.Count(a),
b =
List.Generate(
() => [i = 0, r = Record.AddField(a{0}, "Balance", try Number.From(tbl{0}[Balance]) otherwise 0)],
(x) => x[i] < n,
(x) => [i = x[i] + 1, r = Record.AddField(a{0}, "Balance", x[r][Balance] + a{i}[Debit] - a{i}[Credit])],
(x) => x[r]
),
z = Table.FromRecords(b)][z],
gr = Table.Group(bs, {"Entity", "Account_No"}, {{"all", each f_bs(Table.Sort(_, "Period"))}}),
out = Table.SelectRows(Source, each [Statement] = "IS") & Table.Combine(gr[all])
in
out

[JRParker]

AlienSX, been pressed with a deadline, but very anxious to comprehend and test.  The one thing I realized is that I don't want to distinguish between between Statement BS or IS; I need to do the same for all accounts. So to rephrase my original intent:

>Add column calculating ending Balance for each Account for each Period, for each Entity in the TB table
> The Balance is the the prior period (month) Balance plus the Debits - Credits for each Period.
>The TB table has various Periods (YYYYMM) of various Entities
> Commence with an opening Balance at earliest Period, calculating ending Balance going forward (If it matters, the opening period is 201702.)
>Fields Text accept Debit and Credit Fixed Decimal
>Presumably, one would create a virtual table of records Grouped by Entity by Account_No and process each set of Entity-Account_No with the different Periods

>Bold red Balance values are opening Balances in first Period; bold red ?? is the challange.  

...would really appreciate if you could modify.  This will result in a TB Balance that nets to zero.  (While the IS accounts will be cumulative, I can use your formula from your previous help to obtain prior month balance for a given account and calculate the difference. :))  will post updated sample

[JRParker]

for some reason can't post updated data sample, stating exceeding 20k characters... let me know if you need an updated data sample.. thanks again for your help.

[JRParker]

**bleep**.... I'm going to take some time this weekend to comprehend... Thanks so much.... Will get back to you

[DataNinja777]

Hi @JRParker,

You mentioned that Bold red BS Balance values are opening Balances in first Period, but does that mean the balance is before reflecting the debit and credit balances in the same rows, or are they closing balance after reflecting the debit and credit balances in the same row, in the consistent manner with the income statement?  Are you trying to find out the dax formula to get the ??? part calculated?