Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
accounts | YearMonth | Initial bank balance | bank collections | bank disbursements | current month net |
account 1 | 50 | 50 | 10 | 90 | |
account 2 | 60 | 10 | 50 | 20 | |
account 1 | 40 | 70 | -30 | ||
account 2 | 80 | 35 | 45 |
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:
accounts | YearMonth | Initial bank balance | bank collections | bank disbursements | current month net | RunningTotalBalance |
account 1 | 1/1/2020 | 50 | 50 | 10 | 90 | 90 |
account 1 | 2/1/2020 | 40 | 70 | -30 | 60 | |
account 2 | 1/1/2020 | 60 | 10 | 50 | 20 | 20 |
account 2 | 2/1/2020 | 80 | 35 | 45 | 65 |
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:
accounts | YearMonth | Initial bank balance | bank collections | bank disbursements | current month net |
account 1 | 50 | 50 | 10 | 90 | |
account 2 | 60 | 10 | 50 | 20 | |
account 1 | 40 | 70 | -30 | ||
account 2 | 80 | 35 | 45 |
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:
accounts | YearMonth | Initial bank balance | bank collections | bank disbursements | current month net | RunningTotalBalance |
account 1 | 1/1/2020 | 50 | 50 | 10 | 90 | 90 |
account 1 | 2/1/2020 | 40 | 70 | -30 | 60 | |
account 2 | 1/1/2020 | 60 | 10 | 50 | 20 | 20 |
account 2 | 2/1/2020 | 80 | 35 | 45 | 65 |
Great thank you for your time and expertise ! 🙂
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |