Import opening balances into data model with SAGE/Accpac tables

[Peter083]

Hi, can someone please help with the following,

I have an Accpac database and I have imported the relevant journal data tables into a data model. One of the problems that I am having is that balance sheet items require an opening balance which I have in a table called Opening Balances (the Accpac table is called GLAFS) which contains an AccountID a fiscal year only and an opening balance amount. I have imported the journal entries (the Accpac table is called GLPOST) which contains an Account ID, journal date and an amount. I have linked both of these tables to the Chart of Accounts (the Accpac table is called GLAMF) which contains an Account ID, Account Description and Account Type. The Account Type Column in the Chart of Accounts contains a reference of B - Balance Sheet, I - income Statement and R - Retained Income. Fot the sake of completeness I have also created a relationship between the Chart of Accounts table and an Account Group table (the Accpac table is called GLACGRP) with a relationship between ACCTGRPCOD (which allocates the Accounts into various categories such as current assets, revenue, fixed assets etc.. I have also added a calendar table for time intelligence calculations.


Can someone please assist or guide me as to how I can add the opening balances in the GLAFS table to the journal entries in the GLPOST table, the opening balance amounts are always on the first day of the financial year. I can provide an image of the data model if required

Thanks

P

 

[akice]

Opening balance are somewhat interesting as they sort of belong in the time between Dec 31st and Jan 1st. They are not transactions in either year yet obviously need to be included for the Balance Sheet. In my financial statements i ended up using a summary 13 period calendar that included a period called "BB" as the first one (period 0 to be precise).

If you want to use the Time Intelligence functions (which require a full calendar), then you could add a date calculated column to GLAFS:

=DATE(GLAFS[fiscal year], 1,1)

then link GLAFS to your Calendar and to your chart of accounts table. From there it is a simple matter of adding amounts from the separate tables (you can either add them like below, or create separate measures for BB and JE's, then a 3rd measure to add the individual measures together):

Total plus BB:= SUM(GLAFS[BB Amount] )  + SUM(GLPOST[Amount])

then model as you want using this measure. For example, YTD is an easy:

Amount YTD := TOTALYTD( [Total plus BB], Calendar[Date] )

The reason i don't like this is, of course, because 'January' doesn't include just January transactions but the transactions AND the beginning balances. But it will work. Make sense?

 

[Peter083]

Thanks Akice, makes sense and a brilliant solution and it works.

As you can see I am working with data from the GLPOST table in Accpac. I would prefer it if I could access the source data but I can't find the table that contains this data. Do you by any chance know which table or how I can access the source data.

Best Regards

P

 

[akice]

Sorry, I have never worked with Sage/Accpac so can't help.