topic Re: Calculate closing balance using opening balance and net cashflow in DAX Commands and Tips

Calculate closing balance using opening balance and net cashflow

GokilaRaviraj — Tue, 22 Oct 2024 13:39:45 GMT

Hi ,

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

Could anyone suggest a DAX code for this ? Thanks in Advance.

Re: Calculate closing balance using opening balance and net cashflow

lbendlin — Tue, 22 Oct 2024 22:02:25 GMT

Please show the expected outcome based on the sample data you provided.

Re: Calculate closing balance using opening balance and net cashflow

GokilaRaviraj — Wed, 23 Oct 2024 07:47:21 GMT

Hi lbendlin

Here is the expected ouput

Closing Balance
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

Re: Calculate closing balance using opening balance and net cashflow

Anonymous — Thu, 24 Oct 2024 08:26:49 GMT

Hi GokilaRaviraj ,

Based on the data you provided, I think you need to create custom columns. Custom columns are used to determine the values used for accumulation. Here are my tests and pbix files for your reference.

Your date format can not be recognized, I converted it to "YYYYMM" format in YearMonth column. This format makes it easy to compare time.
The NC column is used to get the NetCashFlow of the relevant date.
The CompanyID column is used to check Company order.
The NC_2 column is used to check if date is current date.
The CB_1 column is the cumulative closing balance value obtained by the company.
The CB_2 column is the final cumulative result.

Best regards,

Mengmeng Li

Re: Calculate closing balance using opening balance and net cashflow

GokilaRaviraj — Thu, 24 Oct 2024 08:37:11 GMT

Hi Anonymous

Thanks for your solution.

In my original input table, I have many company names.

How do I write a code for Comapny ID ?

CompanyID = SWITCH('Opening balance'[Company],"A",1,"B",2)

You have given this for A, B companies.

Is there any solution for this company id ? If I could change that, This will work I believe.

Re: Calculate closing balance using opening balance and net cashflow

GokilaRaviraj — Thu, 24 Oct 2024 15:55:12 GMT

Hi Guys lbendlin Anonymous

I have found a DAX code for this. But there is some issue here. This code works perfectly to calculate closing balance for current month. It is trying to fetch current month closing balance to calculate Nov , dec and all other month's. It is failing to add CurrentClosingBalance with NextMonthNetCashFlow.

There is some issue in Return statement. It is checking if month is oct, then returning current month closing balance else it is returning

CurrentClosingBalance + NextMonthNetCashFlow this. This calculation

has to happen iteratively till the end of all other months

I am providing the code which i have written. PLease suggest me a code if you find a solution for this issue, It would be a great help.

ClosingBalanceMeasure =

VAR CurrentCompany = SELECTEDVALUE(Company[Company], "No Company Selected") -- Get the current company

VAR CurrentMonth =

VAR TodayDate = TODAY()

VAR CurrentMonthText = FORMAT(TodayDate, "MMM-YY") -- Get the current month in text format

RETURN

CALCULATE(

MAX('Calendar'[MonthYear]),

FILTER(

'Calendar',

'Calendar'[MonthYear] = CurrentMonthText

)

-- Convert the current month to a date format

VAR CurrentMonthAsDate = DATEVALUE("01-" & LEFT(CurrentMonth, 3) & "-20" & RIGHT(CurrentMonth, 2))

-- Calculate the opening balance for the current month

VAR OpeningBalance =

CALCULATE(

SUM(OpeningCurrentBalance[Value]),

FILTER(

OpeningCurrentBalance,

OpeningCurrentBalance[Company] = CurrentCompany

)

-- Calculate the current month's net cash flow

VAR CurrentNetCashFlow =

CALCULATE(

SUM(NetCashFlow[Value]),

FILTER(

NetCashFlow,

NetCashFlow[Company] = CurrentCompany &&

NetCashFlow[Month] = CurrentMonth

)

-- Calculate the current month's closing balance

VAR CurrentClosingBalance = OpeningBalance + CurrentNetCashFlow

-- Handle previous month's closing balance logic

VAR PreviousMonthAsDate = EOMONTH(CurrentMonthAsDate, -1) -- Get the last day of the previous month

VAR PreviousMonthText = FORMAT(PreviousMonthAsDate, "MMM-YY") -- Convert to text format (e.g., "Sep-24")

-- Calculate previous month's closing balance if it exists

VAR PreviousClosingBalance =

CALCULATE(

SUM(ClosingEstimateBalance[Value]),

FILTER(

ClosingEstimateBalance,

ClosingEstimateBalance[Company] = CurrentCompany &&

ClosingEstimateBalance[Month] = PreviousMonthText

)

-- Calculate next month's net cash flow

VAR NextMonthAsDate = EOMONTH(CurrentMonthAsDate, 0) + 1 -- Get the first day of the next month

VAR NextMonthText = FORMAT(NextMonthAsDate, "MMM-YY") -- Convert to text format (e.g., "Nov-24")

VAR NextMonthNetCashFlow =

CALCULATE(

SUM(NetCashFlow[Value]),

FILTER(

NetCashFlow,

NetCashFlow[Company] = CurrentCompany &&

NetCashFlow[Month] = NextMonthText

)

-- Calculate the final closing balance

RETURN

IF(

ISBLANK(PreviousClosingBalance), -- If there is no previous closing balance (first month case)

CurrentClosingBalance, -- Use the current closing balance

CurrentClosingBalance + NextMonthNetCashFlow -- For other months, calculate using current closing balance + next month's net cash flow

)

It is repeating the same value calculated for current closing balance to all other months.

Thank you in Advance.

Re: Calculate closing balance using opening balance and net cashflow

GokilaRaviraj — Fri, 25 Oct 2024 10:50:10 GMT

Hi Guys,

Is anyone found any solution for this issue ? I am trying out many solutions but nothing works out.

Re: Calculate closing balance using opening balance and net cashflow

DataNinja777 — Sun, 27 Oct 2024 06:04:17 GMT

Hi GokilaRaviraj

Your current output seems confusing to me 🤔. Specifically, I don’t understand how Company B’s opening balance of 10 in October 2024 results in a cumulative (closing) balance of 170 for the same period, given that the Net Cash Flow for the month is only 10. Logically, the closing balance should be 20 for Company B, based on the company-level dimension.

To address this, I recommend creating two dimension tables—one for the calendar and another for the company—and establishing relationships between these dimensions and your opening balance and Net Cash Flow fact tables, as outlined below:

I would then create a cumulative measure for the closing balance using both the NetCashFlow and Opening Balance, as shown below:

Closing Balance = VAR CurrentDate = MAX('Calendar'[Date]) RETURN CALCULATE( SUM(NetCashFlow[Value])+sum('Opening balance'[Value]), FILTER( ALL('Calendar'), 'Calendar'[Date] <= CurrentDate ) )

The resulting output looks like below which differs from your expected output:

From an accounting perspective, the cumulative cash balance is calculated over time. However, your output appears to accumulate across the row index, which may not reflect standard accounting practices.

I have attached an example pbix file.

Best regards,

Re: Calculate closing balance using opening balance and net cashflow

Anonymous — Mon, 28 Oct 2024 08:10:11 GMT

Hi GokilaRaviraj ,

The reason for sorting companies is that you expect the CB value for October of company B in the result to be 170 instead of 180. I treat it as monthly cumulative value determined by both the date and the company, only companies (A) whose date is the current month and whose order is 1 use this rule: CB = OB + NetCashFlow.

If I understand correctly, you should have a table for company sorting, if not, you need to create a company sorting table, you can use DISTINCT to get all the companies and then create a row sequence using the SWITCH function.

Best regards,

Mengmeng Li

Re: Calculate closing balance using opening balance and net cashflow

GokilaRaviraj — Mon, 28 Oct 2024 10:19:02 GMT

Hi DataNinja777

Thanks for your solution. It worked really cool !!