Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
GokilaRaviraj
Helper II
Helper II

Calculating closing balance

‎10-23-2024 03:39 AM

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

CompanyMonthValue
AOct-2410
ANov-2410
ADec-2410
AJan-2510
AFeb-2510
AMar-2510
AApr-2510
AMay-2510
AJun-2510
AJul-2510
AAug-2510
ASep-2510
AOct-2510
ANov-2510
ADec-2510
BOct-2410
BNov-2410
BDec-2410
BJan-2510
BFeb-2510
BMar-2510
BApr-2510
BMay-2510
BJun-2510
BJul-2510
BAug-2510
BSep-2510
BOct-2510
BNov-2510
BDec-2510

This is Opening balance table

CompanyMonthValue
AOct-2410
ANov-240
ADec-240
AJan-250
AFeb-250
AMar-250
AApr-250
AMay-250
AJun-250
AJul-250
AAug-250
ASep-250
AOct-250
ANov-250
ADec-250
BOct-2410
BNov-240
BDec-240
BJan-250
BFeb-250
BMar-250
BApr-250
BMay-250
BJun-250
BJul-250
BAug-250
BSep-250
BOct-250
BNov-250
BDec-250

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;

CompanyMonthValue
AOct-2420
ANov-2430
ADec-2440
AJan-2550
AFeb-2560
AMar-2570
AApr-2580
AMay-2590
AJun-25100
AJul-25110
AAug-25120
ASep-25130
AOct-25140
ANov-25150
ADec-25160
BOct-24170
BNov-24180
BDec-24190
BJan-25200
BFeb-25210
BMar-25220
BApr-25230
BMay-25240
BJun-25250
BJul-25260
BAug-25270
BSep-25280
BOct-25290
BNov-25300
BDec-25310

Could you please suggest a DAX code for this ? 

Thanks in Advance

Message 1 of 5
426 Views
0
1 ACCEPTED SOLUTION
v-xuxinyi-msft
Community Support
Community Support
In response to GokilaRaviraj

‎10-29-2024 03:06 AM

Hi @GokilaRaviraj 

 

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?

vxuxinyimsft_0-1730195857986.png

 

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.

 

vxuxinyimsft_1-1730196195656.png

 

vxuxinyimsft_2-1730196213443.png

 

vxuxinyimsft_3-1730196232302.png

 

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 + _sumOB

 

Output:

vxuxinyimsft_4-1730196305994.png

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Calculating closing balance.pbix
Message 4 of 5
258 Views
0
4 REPLIES 4
123abc
Community Champion
Community Champion

‎10-23-2024 05:29 AM

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

AOct-2420
ANov-2430
ADec-2440
.........
BOct-24170
BNov-24180
BDec-24190

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.

Message 2 of 5
390 Views
0
GokilaRaviraj
Helper II
Helper II
In response to 123abc

‎10-23-2024 07:10 AM

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

CompanyMonthYearValue
Barngruppen Fastigheter ABOct-202410000
Barngruppen Fastigheter ABNov-20240
Barngruppen Fastigheter ABDec-2024-57000
Barngruppen Fastigheter ABJan-20250
Barngruppen Fastigheter ABFeb-20250
Barngruppen Fastigheter ABMar-2025-57000
Barngruppen Fastigheter ABApr-20250
Barngruppen Fastigheter ABMay-20250
Barngruppen Fastigheter ABJun-2025-57000
Barngruppen Fastigheter ABJul-20250
Barngruppen Fastigheter ABAug-20250
Barngruppen Fastigheter ABSep-2025-57000
Barngruppen Fastigheter ABOct-20250
Barngruppen Fastigheter ABNov-20250
Barngruppen Fastigheter ABDec-2025-57000
**bleep**erhage Fastighetsutveckling ABOct-20240
**bleep**erhage Fastighetsutveckling ABNov-20240
**bleep**erhage Fastighetsutveckling ABDec-2024-875000
**bleep**erhage Fastighetsutveckling ABJan-20250
**bleep**erhage Fastighetsutveckling ABFeb-20250
**bleep**erhage Fastighetsutveckling ABMar-2025-875000
**bleep**erhage Fastighetsutveckling ABApr-2025-875000
**bleep**erhage Fastighetsutveckling ABMay-20250
**bleep**erhage Fastighetsutveckling ABJun-2025-875000
**bleep**erhage Fastighetsutveckling ABJul-20250
**bleep**erhage Fastighetsutveckling ABAug-20250
**bleep**erhage Fastighetsutveckling ABSep-2025-875000
**bleep**erhage Fastighetsutveckling ABOct-20250
**bleep**erhage Fastighetsutveckling ABNov-20250
**bleep**erhage Fastighetsutveckling ABDec-2025-875000
Fastighets AB ÄlvdansenOct-20240
Fastighets AB ÄlvdansenNov-20240
Fastighets AB ÄlvdansenDec-2024-215625
Fastighets AB ÄlvdansenJan-20250
Fastighets AB ÄlvdansenFeb-20250
Fastighets AB ÄlvdansenMar-2025-215625
Fastighets AB ÄlvdansenApr-20250
Fastighets AB ÄlvdansenMay-20250
Fastighets AB ÄlvdansenJun-2025-215625
Fastighets AB ÄlvdansenJul-20250
Fastighets AB ÄlvdansenAug-20250
Fastighets AB ÄlvdansenSep-2025-215625
Fastighets AB ÄlvdansenOct-20250
Fastighets AB ÄlvdansenNov-20250
Fastighets AB ÄlvdansenDec-2025-215625

This is OpeningBalance table

CompanyMonthYearValue
Älvdansen Bostad ABOct-2491500.4
Älvdansen Bostad ABNov-240
Älvdansen Bostad ABDec-240
Älvdansen Bostad ABJan-250
Älvdansen Bostad ABFeb-250
Älvdansen Bostad ABMar-250
Älvdansen Bostad ABApr-250
Älvdansen Bostad ABMay-250
Älvdansen Bostad ABJun-250
Älvdansen Bostad ABJul-250
Älvdansen Bostad ABAug-250
Älvdansen Bostad ABSep-250
Älvdansen Bostad ABOct-250
Älvdansen Bostad ABNov-250
Älvdansen Bostad ABDec-250
Barngruppen Fastigheter ABOct-24412194.37
Barngruppen Fastigheter ABNov-240
Barngruppen Fastigheter ABDec-240
Barngruppen Fastigheter ABJan-250
Barngruppen Fastigheter ABFeb-250
Barngruppen Fastigheter ABMar-250
Barngruppen Fastigheter ABApr-250
Barngruppen Fastigheter ABMay-250
Barngruppen Fastigheter ABJun-250
Barngruppen Fastigheter ABJul-250
Barngruppen Fastigheter ABAug-250
Barngruppen Fastigheter ABSep-250
Barngruppen Fastigheter ABOct-250
Barngruppen Fastigheter ABNov-250
Barngruppen Fastigheter ABDec-250
Ferhage Fastighetsutveckling ABOct-24777303.15
Ferhage Fastighetsutveckling ABNov-240
Ferhage Fastighetsutveckling ABDec-240
Ferhage Fastighetsutveckling ABJan-250
Ferhage Fastighetsutveckling ABFeb-250
Ferhage Fastighetsutveckling ABMar-250
Ferhage Fastighetsutveckling ABApr-250
Ferhage Fastighetsutveckling ABMay-250
Ferhage Fastighetsutveckling ABJun-250
Ferhage Fastighetsutveckling ABJul-250
Ferhage Fastighetsutveckling ABAug-250
Ferhage Fastighetsutveckling ABSep-250
Ferhage Fastighetsutveckling ABOct-250
Ferhage Fastighetsutveckling ABNov-250
Ferhage Fastighetsutveckling ABDec-250
Fastighets AB ÄlvdansenOct-240
Fastighets AB ÄlvdansenNov-240
Fastighets AB ÄlvdansenDec-240
Fastighets AB ÄlvdansenJan-250
Fastighets AB ÄlvdansenFeb-250
Fastighets AB ÄlvdansenMar-250
Fastighets AB ÄlvdansenApr-250
Fastighets AB ÄlvdansenMay-250
Fastighets AB ÄlvdansenJun-250
Fastighets AB ÄlvdansenJul-250
Fastighets AB ÄlvdansenAug-250
Fastighets AB ÄlvdansenSep-250
Fastighets AB ÄlvdansenOct-250
Fastighets AB ÄlvdansenNov-250
Fastighets AB ÄlvdansenDec-250
Fastighets AB ÄlvdansenOct-241677692.45
Fastighets AB ÄlvdansenNov-240
Fastighets AB ÄlvdansenDec-240
Fastighets AB ÄlvdansenJan-250
Fastighets AB ÄlvdansenFeb-250
Fastighets AB ÄlvdansenMar-250
Fastighets AB ÄlvdansenApr-250
Fastighets AB ÄlvdansenMay-250
Fastighets AB ÄlvdansenJun-250
Fastighets AB ÄlvdansenJul-250
Fastighets AB ÄlvdansenAug-250
Fastighets AB ÄlvdansenSep-250
Fastighets AB ÄlvdansenOct-250
Fastighets AB ÄlvdansenNov-250
Fastighets AB ÄlvdansenDec-250

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

GokilaRaviraj_0-1729691656094.png

Do let me  know if you get a solution. Thanks in Advance.

Message 3 of 5
374 Views
0
v-xuxinyi-msft
Community Support
Community Support
In response to GokilaRaviraj

‎10-29-2024 03:06 AM

Hi @GokilaRaviraj 

 

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?

vxuxinyimsft_0-1730195857986.png

 

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.

 

vxuxinyimsft_1-1730196195656.png

 

vxuxinyimsft_2-1730196213443.png

 

vxuxinyimsft_3-1730196232302.png

 

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 + _sumOB

 

Output:

vxuxinyimsft_4-1730196305994.png

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Calculating closing balance.pbix
Message 4 of 5
259 Views
0
GokilaRaviraj
Helper II
Helper II
In response to v-xuxinyi-msft

‎10-29-2024 06:59 AM

Hi @v-xuxinyi-msft Sorry for the confusion. That was by mistake. I tried this code. It works perfectly. Thanks for your solution !!

 

Message 5 of 5
241 Views

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

View All