cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sebinantony
New Member

calculate opening balance, sum of debit/credit and closing balance within date

Hi,

 

I need help!

I have the GL transactions of all main accounts, below is the sample data :sample1.JPG

 

I want to write measures for calculating:

Opening balance: This will be sum of all transaction before the start date of the date range filter for the specific main account 

Debit : This will be  sum of all values under 'debit' for the same main account with in the the date range 

Credit: This will be sum of all values under 'credit' for the same main account with in the date range 

Closing balance: This will be calculated as sum of opening balance, debit and credit for the specific main account 

 

The resulting table should look like:

sample2.JPG

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Try this with Date Calendar and join it Accounting date

@sebinantony 

The opening is sum of debit and credit till last month

The closing is sum of debit and credit till this month

All are new measures

Debit = SUM(Table[Debit])

Credit =SUM(Table[Credit])

Opening balance = CALCULATE(SUM(Table[Debit]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,Month))))
+ CALCULATE(SUM(Table[Credit]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,Month))))

closing balance = CALCULATE(SUM(Table[Debit]),filter(date,date[date] <=maxx(date,date[date])))
+ CALCULATE(SUM(Table[Credit]),filter(date,date[date] <=maxx(date,date[date])))

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

Try this with Date Calendar and join it Accounting date

@sebinantony 

The opening is sum of debit and credit till last month

The closing is sum of debit and credit till this month

All are new measures

Debit = SUM(Table[Debit])

Credit =SUM(Table[Credit])

Opening balance = CALCULATE(SUM(Table[Debit]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,Month))))
+ CALCULATE(SUM(Table[Credit]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,Month))))

closing balance = CALCULATE(SUM(Table[Debit]),filter(date,date[date] <=maxx(date,date[date])))
+ CALCULATE(SUM(Table[Credit]),filter(date,date[date] <=maxx(date,date[date])))

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

@amitchandak 

 

Hi Thanks for helping! I am able to get the measured values for debit and credit but can you please tell me how to "Try this with Date Calendar and join it Accounting date" as I am not getting the opening and closing balance values 

 

Thanks!

Sebin

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.