Forum Discussion

GokilaRaviraj's avatar
GokilaRaviraj
Icon for Helper II rankHelper II
1 year ago
Solved

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 ...
"DAX " "DAX help" 'Need help' 'DAX'
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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?

     

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

     

    Output:

     

    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.

123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
1 year ago

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.

GokilaRaviraj's avatar
GokilaRaviraj
Icon for Helper II rankHelper II
1 year ago

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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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?

     

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

     

    Output:

     

    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.

    • GokilaRaviraj's avatar
      GokilaRaviraj
      Icon for Helper II rankHelper II
      1 year ago

      Hi Anonymous Sorry for the confusion. That was by mistake. I tried this code. It works perfectly. Thanks for your solution !!