cancel
Showing results for
Did you mean: 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 : 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: 1 ACCEPTED SOLUTION  Super User

Try this with Date Calendar and join it Accounting date

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])

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])))

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

Try this with Date Calendar and join it Accounting date

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])

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])))

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! New Member

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   