Calculate closing balance using opening balance and net cashflow

[GokilaRaviraj]

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.

[GokilaRaviraj]

Hi Guys @lbendlin @v-mengmli-msft 

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.

[GokilaRaviraj]

Hi Guys,

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

[v-mengmli-msft]

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

[GokilaRaviraj]

Hi @v-mengmli-msft 

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.

[v-mengmli-msft]

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

[lbendlin]

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

[GokilaRaviraj]

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

[DataNinja777 · Accepted solution]

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,

[GokilaRaviraj]

Hi @DataNinja777 

Thanks for your solution. It worked really cool !!