Trial Balance Power BI Reports for MYOB

Home / Report Templates / Trial Balance Power BI Reports for MYOB

The Trial Balance Power BI Reports for MYOB is a simple dashboard that provides a series of reports to display the net debits and credits values for your chart of accounts in Power BI. For an Excel version of this report, see the Trial Balance Excel Reports for MYOB.

The dashboard includes:

  • Trial Balance
  • Trial Balance by Month
  • Trial Balance YTD by Month
  • Trial Balance by Company

Not only this, but it can also consolidate data from multiple companies (data files) when using the Consolidation OData Feed.

  • Trial for Balance Report for MYOB
  • Trial for Balance Report by Month for MYOB
  • Trial for Balance Report by Company for MYOB

While this is a fully working and standalone dashboard, it can also be used as a starting point that can be further customised or modified as required to fit whatever business rules you need. It is not locked in any way. Because of this, it can also be used to learn how to create power queries, how to create DAX formulas, etc.

Reports Included

Trial Balance

The Trial Balance report is a simple report that displays Accounts down the page and Debit, Credit, Debit YTD and Credit YTD across the page.

You can filter this report by company, year and month.

Trial Balance by Month

The Trial Balance by Month report is a simple report that displays Accounts down the page and months across the page. The values are the net movement (debit less credit) for the month.

You can filter this report by company, year and month.

Trial Balance YTD by Month

The Trial Balance YTD by Month report is a simple report that displays Accounts down the page and months across the page. The values displayed depend on the type of account:

  • Profit and loss accounts show the year to date net movement (debit less credit) for the month.
  • Balance sheet accounts show the closing balance for the month.

You can filter this report by company, year and month.

Trial Balance by Company

The Trial Balance by Company report is a simple report that displays Accounts down the page and companies across the page. For each company, 4 values are displayed:

  • Debit
  • Credit
  • Debit YTD
  • Credit YTD

You can filter this report by company, year and month.

Requirements

To use the report, you will need to:

  1. Have an OdataLink Subscription or Trial Account.
  2. Connect OdataLink to your MYOB company.
  3. Configure a Model that includes the following endpoints:

Download and Setup

You can download the Power BI templates from the following links:

Once downloaded, you can connect it to your own data using this article.

How to Refresh in Power BI Desktop

To refresh it in Power BI Desktop, follow these steps.

  1. Under the Home tab, go to Transform Data and click on Edit Parameters
  2. Enter the Date Start and Date End for the months you wish to report on.
    This can be any date range but the larger it is, the longer the refresh will take to run.
  3. Click OK to close the parameters dialogue. Click the Refresh button if it doesn’t automatically refresh and wait for the data to download.

Understanding the power queries used to calculate the trial balance values

Calculating the trial balance data correctly is a bit more complicated. This is where the Internal_TrialBalance Power Query comes in. It breaks the process into five operations.

  1. Calculating the movement values
  2. Correcting the current earnings so as not to double count values
  3. Including the opening balance values
  4. Correcting the retained earnings values for future years

Calculating the movement values

For starters, it uses the AccountRegister endpoint to get the movement (debit and credit changes) for each account. The steps involved in this processed are prefixed with Init_ and Main_.

Correcting the current earnings so as not to double count values

Once the main values have been calculated, the values posted to current earnings need to be adjusted slightly. Since we want to see the breakdown of income, cost of sales, and expenses; we need to ensure we do not also includes those values in the current earnings movement. Otherwise, we would be counting these values twice. We also cannot outright exclude values posted to current earnings as there may be meaningful journals that need to be retained. A series of steps with prefix of CECorrection_ take care of this. At a high level, these steps:

  1. Identifies the accounts and values used on the profit and loss
  2. Totals the values up by month
  3. And reverses them from the current earnings values

Including the opening balance values

To provide proper trial balance values, we need to show closing balance values for balance sheet accounts. Unfortunately, the AccountRegister endpoint only provide movement values (e.g. the change in values each month). The Internal_TrialBalance_OpeningBalance power query exists to calculates the missing opening values and uses the BalanceSheetSummaryReportMultiPeriodTable to do so.

Correcting the retained earnings values for future years

In addition, until you rollover your MYOB data file, the retained earnings trial balance for future years will be incorrect. They will be missing the journal that transfers the current earnings values to the retained earnings account. MYOB only creates this journal when rolling over the financial year. Because of this, an additional set of logic exists to correct this:

  1. The Internal_AccountingProperties power query identifies the current financial year.
  2. The Internal_DatesByYears power query determines the years need to corrected.
  3. Finally, the steps prefixed with RECorrection_ in the Internal_TrialBalance power query takes care of calculating the adjustment required.

Finally, the steps prefixed with Final_ in the Internal_TrialBalance power query appends all these values together.

Find an issue with the template?

Let us know of any issues you find with the template. We built it to use the least amount of data possible but this might not suit everyone.