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
Want To Learn Power BI
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
Want To Learn Power BI
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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors