Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LukeCH
Regular Visitor

Calculate balance (running total) per account and also for empty dates (for stacked area chart)

Hi all,

I've spent my last week exploring the dephts of Power Query, aiming to process accounting data for a nice stacked visualization of the developments on some bank and liabilitity accounts:

BalanceGraph.PNG
This video tutorial by @MarcelBeug helped me a great deal in achieving this. Starting from a table with an "Inflow" and an "Outflow" value for each transaction, I could calculate the running totals / "Balance" values for each "Account".



As I also needed a "Balance" for each "Date" with no bookings, this is the code I ended up with:

// Calculate Balance (Translated to English)

    OnlyBalanceAccounts = Table.SelectRows(Input, each (Number.FromText(Text.At([Account Number], 0)) < 3)),
    WithNetFlow = Table.AddColumn(OnlyBalanceAccounts, "Net Flow", each (if Number.FromText(Text.At([Account Number], 0)) = 1 then [Inflow] - [Outflow] else 0) + (if Number.FromText(Text.At([Account Number], 0)) = 2 then [Outflow] - [Inflow] else 0), type number),

    OnlyDateColumn = OnlyBalanceAccounts[Date],
    FirstDate = List.Min(OnlyDateColumn),
    LastDate = List.Max(OnlyDateColumn),
    NumberOfDates = Number.From(LastDate - FirstDate) +1,
    EachDate = List.Dates(FirstDate, NumberOfDates, #duration(1, 0, 0, 0)),
    EachDateTable = Table.FromColumns({EachDate},{"Date"}),

    OnlyAccountColumn = OnlyBalanceAccounts[Account Number],
    WithoutDuplicates = List.Distinct(OnlyAccountColumn),
    EachAccountTable = Table.FromColumns({WithoutDuplicates},{"Account Number"}),

    CombinedTable = Table.AddColumn(EachAccountTable, "Date", each EachDateTable),
    ExpandedDatesXAccounts = Table.ExpandTableColumn(CombinedTable, "Date", {"Date"}, {"Date"}),

    Stacked = Table.Combine({WithNetFlow , ExpandedDatesXAccounts}),
    Sorted = Table.Buffer(Table.Sort(Stacked,{{"Account Number", Order.Ascending}, {"Date", Order.Ascending}, {"OriginalID", Order.Ascending}})),

    GroupedWithFunction = Table.Group(Sorted, {"Account Number"}, {{"Tables", AddBalance, Value.Type(AddBalance(Sorted))}}),
    Expanded = Table.ExpandTableColumn(GroupedWithFunction, "Tables", {"OriginalID", "Date", "Reference", "Booking Description", "Document Link", "Business Partner", "Project", "Item Description", "Other Stuff", "Currency", "FX Rate", "Tag is ""Local""", "VAT Category", "Booking Type is ""Reset""", "Inflow", "Outflow", "Net Flow", "Balance"}, {"OriginalID", "Date", "Reference", "Booking Description", "Document Link", "Business Partner", "Project", "Item Description", "Other Stuff", "Currency", "FX Rate", "Tag is ""Local""", "VAT Category", "Booking Type is ""Reset""", "Inflow", "Outflow", "Net Flow", "Balance"}),
    Cleaned = Table.RemoveColumns(Expanded ,{"Net Flow"}),

    OnlyPLAccounts = Table.SelectRows(Input, each (Number.FromText(Text.At([Account Number], 0)) >= 3)),
    AllAccounts = Table.Combine({Cleaned, OnlyPLAccounts})


And the function for the "Balance" calculation:

(Table as table) as table =>
let
    Source = Table,
    NetFlows = List.Buffer(Source[Net Flow]),
    Custom = Source,
    WithIndex = Table.AddIndexColumn(Custom, "Index", 1, 1),
    WithBalance = Table.AddColumn(WithIndex, "Balance", each Number.Round(List.Sum(List.FirstN(NetFlows,[Index])),2), type number),
    Cleaned = Table.RemoveColumns(WithBalance,{"Index"})
in
    Cleaned

 
However, there are some things that I didnt'get:

 

  1. Would this be achievable without using a function, theoretically? I've tried this for quite some time, but didn't find the right way to refer to the "Net Flow" column in the nested tables (all grouped by "Account") for the List.Sum function (as a list I guess).
  2. How could we filter the redundant dates from ExpandedDatesXAccounts, meaning those combinations of Date and Account for which we actually have at least one transaction in the source data?
  3. Can we calculate the "Balance" only for the last transaction (Max "OriginalIID") of each "Date"? This would improve visualization in PowerBI - so far I got to live with "Average" as aggregation, while each date's end balance per account is the only thing investors Heartabout.

Grateful for everything I can learn from you!

 

yours,
Luke

2 REPLIES 2
LukeCH
Regular Visitor

Hi all,

 

I've spent my last week exploring the dephts of Power Query, aiming to process accounting data for a nice stacked visualization of the developments on some bank and liabilitity accounts:

 

BalanceGraph.PNG

 

This video tutorial by @MarcelBeug helped me a great deal in achieving this. Starting from a table with an "Inflow" and an "Outflow" value for each transaction, I could calculate the running totals / "Balance" values for each "Account".

 

 

As I also needed a "Balance" for each "Date" with no bookings, this is the code I ended up with:

 

// Calculate Balance (Translated to English)

    OnlyBalanceAccounts = Table.SelectRows(Input, each (Number.FromText(Text.At([Account Number], 0)) < 3)),
    WithNetFlow = Table.AddColumn(OnlyBalanceAccounts, "Net Flow", each (if Number.FromText(Text.At([Account Number], 0)) = 1 then [Inflow] - [Outflow] else 0) + (if Number.FromText(Text.At([Account Number], 0)) = 2 then [Outflow] - [Inflow] else 0), type number),

    OnlyDateColumn = OnlyBalanceAccounts[Date],
    FirstDate = List.Min(OnlyDateColumn),
    LastDate = List.Max(OnlyDateColumn),
    NumberOfDates = Number.From(LastDate - FirstDate) +1,
    EachDate = List.Dates(FirstDate, NumberOfDates, #duration(1, 0, 0, 0)),
    EachDateTable = Table.FromColumns({EachDate},{"Date"}),

    OnlyAccountColumn = OnlyBalanceAccounts[Account Number],
    WithoutDuplicates = List.Distinct(OnlyAccountColumn),
    EachAccountTable = Table.FromColumns({WithoutDuplicates},{"Account Number"}),

    CombinedTable = Table.AddColumn(EachAccountTable, "Date", each EachDateTable),
    ExpandedDatesXAccounts = Table.ExpandTableColumn(CombinedTable, "Date", {"Date"}, {"Date"}),

    Stacked = Table.Combine({WithNetFlow , ExpandedDatesXAccounts}),
    Sorted = Table.Buffer(Table.Sort(Stacked,{{"Account Number", Order.Ascending}, {"Date", Order.Ascending}, {"OriginalID", Order.Ascending}})),

    GroupedWithFunction = Table.Group(Sorted, {"Account Number"}, {{"Tables", AddBalance, Value.Type(AddBalance(Sorted))}}),
    Expanded = Table.ExpandTableColumn(GroupedWithFunction, "Tables", {"OriginalID", "Date", "Reference", "Booking Description", "Document Link", "Business Partner", "Project", "Item Description", "Other Stuff", "Currency", "FX Rate", "Tag is ""Local""", "VAT Category", "Booking Type is ""Reset""", "Inflow", "Outflow", "Net Flow", "Balance"}, {"OriginalID", "Date", "Reference", "Booking Description", "Document Link", "Business Partner", "Project", "Item Description", "Other Stuff", "Currency", "FX Rate", "Tag is ""Local""", "VAT Category", "Booking Type is ""Reset""", "Inflow", "Outflow", "Net Flow", "Balance"}),
    Cleaned = Table.RemoveColumns(Expanded ,{"Net Flow"}),

    OnlyPLAccounts = Table.SelectRows(Input, each (Number.FromText(Text.At([Account Number], 0)) >= 3)),
    AllAccounts = Table.Combine({Cleaned, OnlyPLAccounts})


And the function for the "Balance" calculation:
 

(Table as table) as table =>
let
    Source = Table,
    NetFlows = List.Buffer(Source[Net Flow]),
    Custom = Source,
    WithIndex = Table.AddIndexColumn(Custom, "Index", 1, 1),
    WithBalance = Table.AddColumn(WithIndex, "Balance", each Number.Round(List.Sum(List.FirstN(NetFlows,[Index])),2), type number),
    Cleaned = Table.RemoveColumns(WithBalance,{"Index"})
in
    Cleaned

 

However, there are some things that I didnt'get:

 

  1. Would this be achievable without using a function, theoretically? I've tried this for quite some time, but didn't find the right way to refer to the "Net Flow" column in the nested tables (all grouped by "Account") for the List.Sum function (as a list I guess).
  2. How could we filter the redundant dates from ExpandedDatesXAccounts, meaning those combinations of Date and Account for which we actually have at least one transaction in the source data?
  3. Can we calculate the "Balance" only for the last transaction (Max "OriginalIID") of each "Date"? This would improve visualization in PowerBI - so far I got to live with "Average" as aggregation, while each date's end balance per account is the only thing investors Heart about.

Grateful for everything I can learn from you!

 

yours,
Luke

v-shex-msft
Community Support
Community Support

HI @LukeCH,

 

#1, If you mean extract value from multiple iterators, I think you need to use custom function to extract value from external looping.

#2, Please share some sample data to help us clarify your data structure for test.

#3, You can try to write a measure to calculate result based on last value.(if you are work with table visual, you can setup summary mode as 'last' to show last records based current category)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.