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 !!