Forum Discussion
Calculating closing balance
Hi dufoq3 ,
I have been struggling with creating a DAX code for this.
I have given below sample input tables
This is NetCashFlow table which is a calculated table
| Company | Month | Value |
| A | Oct-24 | 10 |
| A | Nov-24 | 10 |
| A | Dec-24 | 10 |
| A | Jan-25 | 10 |
| A | Feb-25 | 10 |
| A | Mar-25 | 10 |
| A | Apr-25 | 10 |
| A | May-25 | 10 |
| A | Jun-25 | 10 |
| A | Jul-25 | 10 |
| A | Aug-25 | 10 |
| A | Sep-25 | 10 |
| A | Oct-25 | 10 |
| A | Nov-25 | 10 |
| A | Dec-25 | 10 |
| B | Oct-24 | 10 |
| B | Nov-24 | 10 |
| B | Dec-24 | 10 |
| B | Jan-25 | 10 |
| B | Feb-25 | 10 |
| B | Mar-25 | 10 |
| B | Apr-25 | 10 |
| B | May-25 | 10 |
| B | Jun-25 | 10 |
| B | Jul-25 | 10 |
| B | Aug-25 | 10 |
| B | Sep-25 | 10 |
| B | Oct-25 | 10 |
| B | Nov-25 | 10 |
| B | Dec-25 | 10 |
This is Opening balance table
| Company | Month | Value |
| A | Oct-24 | 10 |
| A | Nov-24 | 0 |
| A | Dec-24 | 0 |
| A | Jan-25 | 0 |
| A | Feb-25 | 0 |
| A | Mar-25 | 0 |
| A | Apr-25 | 0 |
| A | May-25 | 0 |
| A | Jun-25 | 0 |
| A | Jul-25 | 0 |
| A | Aug-25 | 0 |
| A | Sep-25 | 0 |
| A | Oct-25 | 0 |
| A | Nov-25 | 0 |
| A | Dec-25 | 0 |
| B | Oct-24 | 10 |
| B | Nov-24 | 0 |
| B | Dec-24 | 0 |
| B | Jan-25 | 0 |
| B | Feb-25 | 0 |
| B | Mar-25 | 0 |
| B | Apr-25 | 0 |
| B | May-25 | 0 |
| B | Jun-25 | 0 |
| B | Jul-25 | 0 |
| B | Aug-25 | 0 |
| B | Sep-25 | 0 |
| B | Oct-25 | 0 |
| B | Nov-25 | 0 |
| B | Dec-25 | 0 |
In this table months are dynamically getting populated using my calendar table, wherein it has months from Current month + remaining months of the current year + next year all the months.
In my original table, I have multiple company names in NetcashFlow and openingbalance tables.
Closing balance report should include companies from both the tables, for current month, it has value in net cashflow and opening balance tables, we can sum it up directly. CB = OB + NetCashFlow.
from next month onwards, Nov CB= OCT CB + Nov Netcashflow
Dec CB= Nov CB + Dec Netcashflow , This logic continues until the last month.
Sample output should contain company, Monthyear, closing balance
expected output;
| Company | Month | Value |
| A | Oct-24 | 20 |
| A | Nov-24 | 30 |
| A | Dec-24 | 40 |
| A | Jan-25 | 50 |
| A | Feb-25 | 60 |
| A | Mar-25 | 70 |
| A | Apr-25 | 80 |
| A | May-25 | 90 |
| A | Jun-25 | 100 |
| A | Jul-25 | 110 |
| A | Aug-25 | 120 |
| A | Sep-25 | 130 |
| A | Oct-25 | 140 |
| A | Nov-25 | 150 |
| A | Dec-25 | 160 |
| B | Oct-24 | 170 |
| B | Nov-24 | 180 |
| B | Dec-24 | 190 |
| B | Jan-25 | 200 |
| B | Feb-25 | 210 |
| B | Mar-25 | 220 |
| B | Apr-25 | 230 |
| B | May-25 | 240 |
| B | Jun-25 | 250 |
| B | Jul-25 | 260 |
| B | Aug-25 | 270 |
| B | Sep-25 | 280 |
| B | Oct-25 | 290 |
| B | Nov-25 | 300 |
| B | Dec-25 | 310 |
Could you please suggest a DAX code for this ?
Thanks in Advance
Thanks for the reply from 123abc .
First of all, I want to confirm your request with you. Why is the value of B in October 170? Is it the value of A in December plus the value of B in October in the OB table?
The following test is for your reference.
Create a calculated column in each of the two tables, and then create a relationship between the two tables based on this column.
Create a measure as follows
Measure = VAR _sumN = CALCULATE(SUM([Value]), FILTER(ALL(NetCashFlow), 'NetCashFlow'[Month] <= MAX([Month]) && [Company] = MAX([Company]))) VAR _sumOB = CALCULATE(SUM(OpeningBalance[Value]), FILTER(ALL(OpeningBalance), [Month] <= MAX([Month]) && [Company] = MAX([Company]))) RETURN _sumN + _sumOBOutput:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
You can try below code:
Closing Balance =
VAR CurrentMonth = NetCashFlow[Month]
VAR CurrentCompany = NetCashFlow[Company]
VAR OpeningBalance =
CALCULATE(
MAX(OpeningBalance[Value]),
OpeningBalance[Company] = CurrentCompany,
OpeningBalance[Month] = CurrentMonth
)
VAR NetCashFlowForCurrentMonth =
CALCULATE(
SUM(NetCashFlow[Value]),
NetCashFlow[Company] = CurrentCompany,
NetCashFlow[Month] = CurrentMonth
)
VAR PreviousMonthClosingBalance =
CALCULATE(
MAXX(
FILTER(
NetCashFlow,
NetCashFlow[Company] = CurrentCompany &&
NetCashFlow[Month] < CurrentMonth
),
[Closing Balance]
)
)
RETURN
IF(
ISBLANK(PreviousMonthClosingBalance),
OpeningBalance + NetCashFlowForCurrentMonth,
PreviousMonthClosingBalance + NetCashFlowForCurrentMonth
)Ensure you have the proper relationship between tables.
This should provide the expected output where the closing balance is calculated dynamically for each company and month:
Company Month Closing Balance
A Oct-24 20 A Nov-24 30 A Dec-24 40 ... ... ... B Oct-24 170 B Nov-24 180 B Dec-24 190 This solution should meet your requirement for calculating closing balances month by month in Power BI.
please share PBIX FILE with data if prob not solved.
Hi 123abc
This code gives an error in first 2 lines and in
[ClosingBalance]. Unfortunately there is no option to share the pbix file for me.
This is NetCashFlow table
Company MonthYear Value Barngruppen Fastigheter AB Oct-2024 10000 Barngruppen Fastigheter AB Nov-2024 0 Barngruppen Fastigheter AB Dec-2024 -57000 Barngruppen Fastigheter AB Jan-2025 0 Barngruppen Fastigheter AB Feb-2025 0 Barngruppen Fastigheter AB Mar-2025 -57000 Barngruppen Fastigheter AB Apr-2025 0 Barngruppen Fastigheter AB May-2025 0 Barngruppen Fastigheter AB Jun-2025 -57000 Barngruppen Fastigheter AB Jul-2025 0 Barngruppen Fastigheter AB Aug-2025 0 Barngruppen Fastigheter AB Sep-2025 -57000 Barngruppen Fastigheter AB Oct-2025 0 Barngruppen Fastigheter AB Nov-2025 0 Barngruppen Fastigheter AB Dec-2025 -57000 **bleep**erhage Fastighetsutveckling AB Oct-2024 0 **bleep**erhage Fastighetsutveckling AB Nov-2024 0 **bleep**erhage Fastighetsutveckling AB Dec-2024 -875000 **bleep**erhage Fastighetsutveckling AB Jan-2025 0 **bleep**erhage Fastighetsutveckling AB Feb-2025 0 **bleep**erhage Fastighetsutveckling AB Mar-2025 -875000 **bleep**erhage Fastighetsutveckling AB Apr-2025 -875000 **bleep**erhage Fastighetsutveckling AB May-2025 0 **bleep**erhage Fastighetsutveckling AB Jun-2025 -875000 **bleep**erhage Fastighetsutveckling AB Jul-2025 0 **bleep**erhage Fastighetsutveckling AB Aug-2025 0 **bleep**erhage Fastighetsutveckling AB Sep-2025 -875000 **bleep**erhage Fastighetsutveckling AB Oct-2025 0 **bleep**erhage Fastighetsutveckling AB Nov-2025 0 **bleep**erhage Fastighetsutveckling AB Dec-2025 -875000 Fastighets AB Älvdansen Oct-2024 0 Fastighets AB Älvdansen Nov-2024 0 Fastighets AB Älvdansen Dec-2024 -215625 Fastighets AB Älvdansen Jan-2025 0 Fastighets AB Älvdansen Feb-2025 0 Fastighets AB Älvdansen Mar-2025 -215625 Fastighets AB Älvdansen Apr-2025 0 Fastighets AB Älvdansen May-2025 0 Fastighets AB Älvdansen Jun-2025 -215625 Fastighets AB Älvdansen Jul-2025 0 Fastighets AB Älvdansen Aug-2025 0 Fastighets AB Älvdansen Sep-2025 -215625 Fastighets AB Älvdansen Oct-2025 0 Fastighets AB Älvdansen Nov-2025 0 Fastighets AB Älvdansen Dec-2025 -215625 This is OpeningBalance table
Company MonthYear Value Älvdansen Bostad AB Oct-24 91500.4 Älvdansen Bostad AB Nov-24 0 Älvdansen Bostad AB Dec-24 0 Älvdansen Bostad AB Jan-25 0 Älvdansen Bostad AB Feb-25 0 Älvdansen Bostad AB Mar-25 0 Älvdansen Bostad AB Apr-25 0 Älvdansen Bostad AB May-25 0 Älvdansen Bostad AB Jun-25 0 Älvdansen Bostad AB Jul-25 0 Älvdansen Bostad AB Aug-25 0 Älvdansen Bostad AB Sep-25 0 Älvdansen Bostad AB Oct-25 0 Älvdansen Bostad AB Nov-25 0 Älvdansen Bostad AB Dec-25 0 Barngruppen Fastigheter AB Oct-24 412194.37 Barngruppen Fastigheter AB Nov-24 0 Barngruppen Fastigheter AB Dec-24 0 Barngruppen Fastigheter AB Jan-25 0 Barngruppen Fastigheter AB Feb-25 0 Barngruppen Fastigheter AB Mar-25 0 Barngruppen Fastigheter AB Apr-25 0 Barngruppen Fastigheter AB May-25 0 Barngruppen Fastigheter AB Jun-25 0 Barngruppen Fastigheter AB Jul-25 0 Barngruppen Fastigheter AB Aug-25 0 Barngruppen Fastigheter AB Sep-25 0 Barngruppen Fastigheter AB Oct-25 0 Barngruppen Fastigheter AB Nov-25 0 Barngruppen Fastigheter AB Dec-25 0 Ferhage Fastighetsutveckling AB Oct-24 777303.15 Ferhage Fastighetsutveckling AB Nov-24 0 Ferhage Fastighetsutveckling AB Dec-24 0 Ferhage Fastighetsutveckling AB Jan-25 0 Ferhage Fastighetsutveckling AB Feb-25 0 Ferhage Fastighetsutveckling AB Mar-25 0 Ferhage Fastighetsutveckling AB Apr-25 0 Ferhage Fastighetsutveckling AB May-25 0 Ferhage Fastighetsutveckling AB Jun-25 0 Ferhage Fastighetsutveckling AB Jul-25 0 Ferhage Fastighetsutveckling AB Aug-25 0 Ferhage Fastighetsutveckling AB Sep-25 0 Ferhage Fastighetsutveckling AB Oct-25 0 Ferhage Fastighetsutveckling AB Nov-25 0 Ferhage Fastighetsutveckling AB Dec-25 0 Fastighets AB Älvdansen Oct-24 0 Fastighets AB Älvdansen Nov-24 0 Fastighets AB Älvdansen Dec-24 0 Fastighets AB Älvdansen Jan-25 0 Fastighets AB Älvdansen Feb-25 0 Fastighets AB Älvdansen Mar-25 0 Fastighets AB Älvdansen Apr-25 0 Fastighets AB Älvdansen May-25 0 Fastighets AB Älvdansen Jun-25 0 Fastighets AB Älvdansen Jul-25 0 Fastighets AB Älvdansen Aug-25 0 Fastighets AB Älvdansen Sep-25 0 Fastighets AB Älvdansen Oct-25 0 Fastighets AB Älvdansen Nov-25 0 Fastighets AB Älvdansen Dec-25 0 Fastighets AB Älvdansen Oct-24 1677692.45 Fastighets AB Älvdansen Nov-24 0 Fastighets AB Älvdansen Dec-24 0 Fastighets AB Älvdansen Jan-25 0 Fastighets AB Älvdansen Feb-25 0 Fastighets AB Älvdansen Mar-25 0 Fastighets AB Älvdansen Apr-25 0 Fastighets AB Älvdansen May-25 0 Fastighets AB Älvdansen Jun-25 0 Fastighets AB Älvdansen Jul-25 0 Fastighets AB Älvdansen Aug-25 0 Fastighets AB Älvdansen Sep-25 0 Fastighets AB Älvdansen Oct-25 0 Fastighets AB Älvdansen Nov-25 0 Fastighets AB Älvdansen Dec-25 0 I did a union function to bring the closing balance table, since my netcashflow table is a calculated table, I have a closing balance value in Oct month for all the companies
Expected output should be in a same format. May be you can load these data and try implementing solution for this. Below is my data model
Do let me know if you get a solution. Thanks in Advance.
Thanks for the reply from 123abc .
First of all, I want to confirm your request with you. Why is the value of B in October 170? Is it the value of A in December plus the value of B in October in the OB table?
The following test is for your reference.
Create a calculated column in each of the two tables, and then create a relationship between the two tables based on this column.
Create a measure as follows
Measure = VAR _sumN = CALCULATE(SUM([Value]), FILTER(ALL(NetCashFlow), 'NetCashFlow'[Month] <= MAX([Month]) && [Company] = MAX([Company]))) VAR _sumOB = CALCULATE(SUM(OpeningBalance[Value]), FILTER(ALL(OpeningBalance), [Month] <= MAX([Month]) && [Company] = MAX([Company]))) RETURN _sumN + _sumOBOutput:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
