Opening & Closing balances

[ravjay]

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

[amitchandak]

@ravjay , refer these can help

openingbalancemonth, openingbalancequarter, openingbalanceyear - https://youtu.be/6lzYOXI5wfo

closingbalancemonth, closingbalancequarter, closingbalanceyear- https://youtu.be/yPQ9UV37LOU

[ravjay]

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