topic Re: Opening and Closing Accounting Balance in DAX Commands and Tips

Opening and Closing Accounting Balance

gerardotrejogcg — Mon, 04 Sep 2023 22:14:36 GMT

I have a Trial Balance where I need to calculate the Opening and Closing Balance.

I did calculate the timeframes to be calculated as well as the Balance Amount with below definitions but I'm not able to get the previous month calculation. Please help !!!!

_Balance Amount Transactional =

SUMX ( 'General Ledger', ABS( 'General Ledger'[Debit] ) - ABS ( 'General Ledger'[Credit] ))

_Balance Amount Transactional M-1 =

CALCULATE ( _Balance Amount Transactional,

FILTER( ALL ( 'Date' ), 'Date'[Date] <= LASTDATE ( PREVIOUSMONTH( 'General Ledger'[TransDate] ) ) )

_Starting Balance =

VAR _FirstDate = FIRSTDATE ( ALL ( 'Date'[Date] ) )

VAR _FirstDatePrevMonth = FIRSTDATE ( PREVIOUSMONTH( 'General Ledger'[TransDate] ) )

VAR _LastDate = LASTDATE ( 'Date'[Date] ) -- LASTDATE ( 'General Ledger'[TransDate] )

VAR _LastDatePreMOnth = LASTDATE ( PREVIOUSMONTH( 'General Ledger'[TransDate] ) )

VAR _FirstTransDate = FIRSTNONBLANK ( 'Date'[Date], [_Balance Amount Transactional] )

RETURN

IF (

_FirstDate = _FirstTransDate,

CALCULATE (

[_Balance Amount Transactional],

DATESBETWEEN ( 'Date'[Date], _FirstDatePrevMonth, _LastDatePreMOnth)

)

Re: Opening and Closing Accounting Balance

DataNinja777 — Mon, 04 Sep 2023 23:49:23 GMT

Hi gerardotrejogcg

In your visualisation screen print, the debit and credit balances have a difference of 5.5 million MXN. Bookkeeping cannot be posted unless debits and credits are balanced and entries recorded in all the GL accounts sums up to zero. Which ERP accounting system did you download the raw data from? As you know, balance sheet GL balances are cumulative sums of transactions since the creation of the GL, while PL account balances are total for the year for the GL, and previous year accumulated profits are recorded in retained earnings in sharehoulders' equity section. In order to prepare this using dax, the above logic needs to be reflected, but in most cases, trial balances are obtainable from ERP or accounting system which your company is using.

Re: Opening and Closing Accounting Balance

gerardotrejogcg — Tue, 05 Sep 2023 04:05:34 GMT

Thanks for the reply DataNinja777 . Yeah you're right, they're not balanced. I'm using AX as my source.

Any insight to calculate the opening and closing balance ?

Re: Opening and Closing Accounting Balance

DataNinja777 — Tue, 05 Sep 2023 05:11:36 GMT

Hi I have never used AX before, but googling (or Bing-ing) indicates that it is Microsoft Dynamics AX. Is this correct? In that case, this is a well know ERP system which has various standard inbuilt reports which also include trial balance. You can of course download all the accounting bookkeeping journal entry records of debits and credits which should add up to zero since the implementation of this ERP system in order to build your hand-made trial balance using the logic of balance sheet GL accounts to cumulate for the lifetime, while cumulating PL GL for the current year only and recording the cumulative previous year PL GL balances in retained earnings GL account, but this is not necessary if you have a standard ERP which already can produce these standard reports. I found the link of how to get TB from Microsoft Dynamics AX's.

Trial balance report (LedgerTrialBalance) | Microsoft Learn

Unless you are using the excel to maintain your own bookkeeping transaction records, building of hand-made trial balance is not necessary because ERP or accounting system will be able to produce such standard reports.😉

gerardotrejogcg

Re: Opening and Closing Accounting Balance

gerardotrejogcg — Sun, 10 Sep 2023 14:22:30 GMT

I fixed the inaccurate Debit and Credit amounts and finally I got the measures as following:

_Closing Balance =

IF ( [_First Date] = [_First Transaction Date],

[_Balance Amount],

CALCULATE (

[_Balance Amount],

PREVIOUSMONTH ( 'Date'[Date] )

) + [_Balance Amount]

)

_Opening Balance =

IF ( [_First Date] = [_First Transaction Date],

CALCULATE (

[_Closing Balance],

PREVIOUSMONTH ( 'Date'[Date] )

)