Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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, 

YEARMONTHOpening CashClosing Cash
2020Janxxxxxx
2020Febxxxxxx
...2022...Janxxxxxx
    

** 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
DAX
opening balance

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak ,

       

      I tried closingbalancemonth function, but it gives the total of transactions done on the last day of the month as the closing balance.

      for example, lets take May 2022,

      I need the total of all transactions up to 30th April (from 01/01/1900) as the Opening balance of May ( -1743616.70)

      and the total of all transactions up to 31st May  (from 01/01/1900) as the Closing balance of May,(1224749.50)

       

       

      But what I realised is that 'closingbalancemonth' provide only a total of transaction done on 31/05/2022 (last day of the month)  (-353,267.28)

       

       

      formula I used 

      CBMonth = CLOSINGBALANCEMONTH(SUM(GL_GLPOST[TR_AMOUNT]), DATE_GL[Date])

       

      **GL_GLPOST[TR_AMOUNT] = total of transactions