URGENT! Dynamic opening balance for Cash Flow Statement
Hey guys, I have a very complicated matrix table for my Cash Flow Statements, the fields are: Row : Cash Flow Statement Item from Level 1 to Level 5 Columns : Date Hierarchy which are Year, Quarter, Month Value : Multiple measures such as Actual, Budget, Balance to go, Forecast, Projection etc Those measures are created from a master [Value] measures which I have used SWITCH function to generate Opening balance, Net Changes, Closing balance and etc. Now, everything correct except for Opening Balance dax, which is as follows. My issue is, when I expand my table to Month, then it is correct But when I collapse it to Quarter or Year Then the Opening Balance become the Sum of opening balance for that quarter or year, not the closing balance for last quarter or last year. Anyone able to help with this please?? I have tried searching with ChatGPT or Deepseek for many days but still unable to resolve this!! Balance B/F = VAR PreviousClosing = CALCULATE( SUMX('d_Consol Data', 'd_Consol Data'[Value]), 'd_Consol Data'[Cash Flow Statement] = "Balance C/F", PREVIOUSMONTH('d_Consol Data'[Date]) ) VAR InitialBalanceBF = CALCULATE( SUMX('d_Consol Data', 'd_Consol Data'[Value]), 'd_Consol Data'[Cash Flow Statement] = "Balance B/F" ) RETURN COALESCE(PreviousClosing, InitialBalanceBF) Total Value Measures is as below, not sure is there anything to do with the filtering context Total Value = VAR SelectedCategory = SELECTEDVALUE('m_CF Statement Hierarchy'[Cash Flow Statement Item]) VAR BalanceCF = CALCULATE( SUMX( 'd_Consol Data', [Balance B/F] + [Net Increase/(Decrease) In Cash & Bank Balances] + [Adjustments in Cash & Bank Balances] ), REMOVEFILTERS('m_CF Statement Hierarchy') ) VAR BalanceCF_Raw = CALCULATE( SUM('d_Consol Data'[Value]), 'd_Consol Data'[Cash Flow Statement] = "Balance C/F" ) VAR Advance = CALCULATE( SUMX('d_Consol Data', [Advance From/(To) Current Account]), REMOVEFILTERS('m_CF Statement Hierarchy') ) VAR ExternalFinanceBalanceCF = [External Financing Balance B/F] + [Net Increased/(Decreased) In External Financing] + [Adjustments in External Financing] RETURN SWITCH( TRUE(), //-------------------- Balance -------------------- SelectedCategory = "Balance B/F", [Balance B/F], SelectedCategory = "Balance C/F", BalanceCF_Raw, //-------------------- Cash & Bank -------------------- SelectedCategory = "Cash & Bank Balances", BalanceCF, SelectedCategory = "Net Cash & Bank Balances", BalanceCF + Advance, //-------------------- External Financing -------------------- SelectedCategory = "External Financing Balance B/F", [External Financing Balance B/F], SelectedCategory = "External Financing Balance C/F", ExternalFinanceBalanceCF, //-------------------- Restricted / Non-Restricted Combined -------------------- SELECTEDVALUE('d_Consol Data'[Restricted Cashflow]) = "No" && SELECTEDVALUE('d_Consol Data'[Cash Flow Statement]) = "Non-Restricted Cashflow", BalanceCF + Advance, SELECTEDVALUE('d_Consol Data'[Restricted Cashflow]) = "Yes" && SELECTEDVALUE('d_Consol Data'[Cash Flow Statement]) = "Restricted Cashflow", BalanceCF + Advance, //-------------------- Default fallback -------------------- SUMX('d_Consol Data', [Value]) )
Dynamic Trial Balance with Opening, Debit, Credit, Net Change and Ending Balance
Hello friends I want to calculate the **Opening** **Balance** for the attached Trial Balance, the opening balance should be dynamic based on the selected date in the dates slicer. Then the **Debit** and **Credit** is the Movement between the selected dates in the **dates slicer** The Net Changes is simple = **Debit** - **Credit** Finally, the **Ending Balance** is the sum of all the transactions until the selected slicer **max date** Thanks in advance, I'm able to achieve the same in Power Query, but I like to create the same using DAX.
Opening & Closing balances
Hi, I'm building a table where I need to get opening & closing cash balances for each month. My GL_GLPBPOST data table has a field for transaction date and I have a separate Date table too. I need to see the Opening & closing balances for each month as shown in the following table, YEAR MONTH Opening Cash Closing Cash 2020 Jan xxx xxx 2020 Feb xxx xxx ...2022 ...Jan xxx xxx ** Opening Cash amount = closing cash amount of the previous month/ DCRC = debit (00) & Credit (01) For this, I've created the following query, but it seems not to return the opening balance, Opening Cash = VAR GL00 = CALCULATE( SUM('GL_GLPBPOST'[AMOUNT]), FILTER( 'GL_GLPBPOST', 'GL_GLPBPOST'[DRCR] = "00" && ('GL_GLPBPOST'[ACCT_CODE] >= "10.10.0000" && ''[ACCT_CODE] <= "10.10.9999") && (DATESBETWEEN('GL_GLPBPOST'[TRAN_DATE], Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1), // Date having Year = Today -1 Month / Month = today -1 Month, and Day = 1 EOMONTH(today(),-1))) // End Of Month effective today() -1 month ) ) ) VAR GLP01 = CALCULATE( SUM('GL_GLPBPOST'[AMOUNT]), FILTER( 'GL_GLPBPOST', 'GL_GLPBPOST'[DRCR] = "01" && ('GL_GLPBPOST'[ACCT_CODE] >= "10.10.0000" && 'GL_GLPBPOST)'[ACCT_CODE] <= "10.10.9999") && (DATESBETWEEN('GL_GLPBPOST_COB (OPBAL)'[TRAN_DATE], Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1), // Date having Year = Today -1 Month / Month = today -1 Month, and Day = 1 EOMONTH(today(),-1))) // End Of Month effective today() -1 month ) ) ) VAR Result = (GL00 + GLP01) RETURN Result
Moving result of a month as an opening balance to the next month
Hi, I would like to move summary result of one month as an opening balance to the next month. Something like that: Jan 2022 Feb 2022 Mar 2022 DAX formula A (closing balance from previous month) 10 20 28 DAX formula B 2 5 DAX formula C 5 1 DAX formula D 3 2 DAX formula E (Summary of A-D) 20 28 I use for "DAX formula A" PREVIOUSMONTH formula but then I get circular dependency between "DAX formula E" (Summary of A-D) and "DAX formula A". "DAX formulas B-D" are calculations of sales, new orders so it is not taken directly from any table, it is also calculated. Could anybody help?Calculate opening balance together with transaction based data into one visual
Hello. I hope you can help me out I have to show both open and closing balances based on a lot of transactions from one dataset. I have to show opening balance up top and closing balance in the buttom of my matrix visual and in between is the kind of transaction which have adjusted the opening balance for the selected month. My data comes in the following diminsions: Date, amount, Transaction group. My problems is, how do i get the opening balance to show closing balance of last month for this month. Currently it is look like this: (Sorry for the red markings but the groups a confidencial) But i need it to go like this: I have gotten the first opening balance by creating a transaction group called opening balance and manuelle putting into my dataset. This is not a year to date visual. I have to see the impact each transaction group have on the "current" opening balance pr. month. Looking forward to hear from you.
