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
chanal
Helper I
Helper I

Initial / closed bank balance to calculate

Hello,

 

I would like some help please with the calculation in DAX for initial bank statements after each period of month.

 

I have figures from collections and disbursements but not initial/closed  balance after each month period.

 

What would you do in Power BI ?

https://drive.google.com/file/d/1NmTLEHO791Ov9CPSWRLh6ZCXKW1zzfkh/view?usp=sharing 

Thank you in advance

1 ACCEPTED SOLUTION

Probably several ways to solve this, but here are my thoughts. You could probably combine steps, but I'll break out for clarity.

 

It looks like your year/month values are actually dates.

 

Step 1 is to convert those to date data type, though I'll leave it named "year/month" here so you can follow. (You'll need to do this because of how you'll calculate balances later).


Step 2 is to combine Table1 and Table2 (treating Table1 like an initial transaction), so you can really just stack them for now.  [In the Data view, Modeling tab, use "New Table"]

 

CombinedTbl = 
VAR AppendedTable =
    UNION(
    SELECTCOLUMNS(
        Table1, 
        "accounts", Table1[accounts], 
        "YearMonth", Table1[month/year],
        "Initial bank balance", BLANK(),
        "bank collections", Table1[bank collections],
        "bank disbursements", Table1[bank disbursements]
    ), 
    SELECTCOLUMNS(
        Table2, 
        "accounts", Table2[accounts],
        "YearMonth", Table2[month/year],
        "Initial bank balance", Table2[Initial bank balance],
        "bank collections", BLANK(),
        "bank disbursements", BLANK()
    )
    )
RETURN AppendedTable

 


Step 3 Summarize that table. [In the Data view, Modeling tab, use "New Table"]

 

SummarizedTable = 
ADDCOLUMNS(
    SUMMARIZECOLUMNS(
        'CombinedTbl'[accounts], 
        'CombinedTbl'[YearMonth], 
        "Initial bank balance", SUM('CombinedTbl'[Initial bank balance]), 
        "bank collections", SUM('CombinedTbl'[bank collections]), 
        "bank disbursements", SUM('CombinedTbl'[bank disbursements])
    ),
    "current month net", [Initial bank balance] + [bank collections] - [bank disbursements]
)

 

Should now look like this:

 

accountsYearMonthInitial bank balancebank collectionsbank disbursementscurrent month net
account 1 50501090
account 2 60105020
account 1  4070-30
account 2  803545

 

Step 4 Create a measure for your cumulative balance

 

 

RunningTotalBalance = 
CALCULATE(
	SUMX(SummarizedTable, [current month net]), 
	FILTER(ALL(SummarizedTable[YearMonth]), SummarizedTable[YearMonth] <= MAXX(SummarizedTable, SummarizedTable[YearMonth]))
)

 

 

Results should now look like this:

accountsYearMonthInitial bank balancebank collectionsbank disbursementscurrent month netRunningTotalBalance
account 11/1/20205050109090
account 12/1/2020 4070-3060
account 21/1/20206010502020
account 22/1/2020 80354565

View solution in original post

2 REPLIES 2

Probably several ways to solve this, but here are my thoughts. You could probably combine steps, but I'll break out for clarity.

 

It looks like your year/month values are actually dates.

 

Step 1 is to convert those to date data type, though I'll leave it named "year/month" here so you can follow. (You'll need to do this because of how you'll calculate balances later).


Step 2 is to combine Table1 and Table2 (treating Table1 like an initial transaction), so you can really just stack them for now.  [In the Data view, Modeling tab, use "New Table"]

 

CombinedTbl = 
VAR AppendedTable =
    UNION(
    SELECTCOLUMNS(
        Table1, 
        "accounts", Table1[accounts], 
        "YearMonth", Table1[month/year],
        "Initial bank balance", BLANK(),
        "bank collections", Table1[bank collections],
        "bank disbursements", Table1[bank disbursements]
    ), 
    SELECTCOLUMNS(
        Table2, 
        "accounts", Table2[accounts],
        "YearMonth", Table2[month/year],
        "Initial bank balance", Table2[Initial bank balance],
        "bank collections", BLANK(),
        "bank disbursements", BLANK()
    )
    )
RETURN AppendedTable

 


Step 3 Summarize that table. [In the Data view, Modeling tab, use "New Table"]

 

SummarizedTable = 
ADDCOLUMNS(
    SUMMARIZECOLUMNS(
        'CombinedTbl'[accounts], 
        'CombinedTbl'[YearMonth], 
        "Initial bank balance", SUM('CombinedTbl'[Initial bank balance]), 
        "bank collections", SUM('CombinedTbl'[bank collections]), 
        "bank disbursements", SUM('CombinedTbl'[bank disbursements])
    ),
    "current month net", [Initial bank balance] + [bank collections] - [bank disbursements]
)

 

Should now look like this:

 

accountsYearMonthInitial bank balancebank collectionsbank disbursementscurrent month net
account 1 50501090
account 2 60105020
account 1  4070-30
account 2  803545

 

Step 4 Create a measure for your cumulative balance

 

 

RunningTotalBalance = 
CALCULATE(
	SUMX(SummarizedTable, [current month net]), 
	FILTER(ALL(SummarizedTable[YearMonth]), SummarizedTable[YearMonth] <= MAXX(SummarizedTable, SummarizedTable[YearMonth]))
)

 

 

Results should now look like this:

accountsYearMonthInitial bank balancebank collectionsbank disbursementscurrent month netRunningTotalBalance
account 11/1/20205050109090
account 12/1/2020 4070-3060
account 21/1/20206010502020
account 22/1/2020 80354565

Great thank you  for your time and expertise ! 🙂

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.

Top Solution Authors