Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ravjay
Frequent Visitor

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. 

 

ravjay_0-1661932750906.png

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
2 REPLIES 2
amitchandak
Super User
Super User

@ravjay , refer these can help

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

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

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)

 

ravjay_0-1662022058166.png

 

formula I used 

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

 

**GL_GLPOST[TR_AMOUNT] = total of transactions

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors