Create a Profit and Loss Report in Power BI using MYOB data and the AccountRegister Endpoint

This article provides an example to create a Profit and Loss Report in Power BI using MYOB data.

The intention is to produce a Profit and Loss report for 12 months.

Profit and Loss Report in Power BI using MYOB data and the AccountRegister Endpoint

This article uses the AccountRegister endpoint downloaded from MYOB into Power BI using OdataLink.

It uses DAX to calculate the necessary measures. Note that the DAX formulas are also compatible with Excel. If you are unfamiliar with DAX, check out our Introduction Tutorial to DAX.

Download the sample report

We provide the sample report created during the video. You can use either to see it in action, or use it as a baseline for creating your own reports. Use the following link to download it.

To see it in action with your own data, you can relink it to your own OData feeds.

About OdataLink

OdataLink is a powerful tool that allows you to download many endpoints of data from MYOB as OData Feeds. These OData feeds are used directly in Power BI or Excel. There is no need for any third-party software. For more information on OdataLink, see odatalink.com.

Create a P&L report in Power BI using MYOB AccountRegister data

There are 5 parts to create the Profit and Loss report.

  1. Download the required data from MYOB
  2. Add relationships between the data in Power BI
  3. Create the DAX columns for the account hierarchy
  4. Create the DAX measures to calculate the correct Profit and Loss values
  5. Create the Profit and Loss report using a matrix visual element

Downloading the required data from MYOB

To start off, you need to download 5 endpoints of information.

Download and shape the DatesByMonth data

The DatesByMonth endpoint provides the column headers used on the report. It includes many commonly used formats in the DisplayStrings column.

DatesByMonth sample data

To shape the DatesByMonth endpoint, apply the following steps:

  1. Filter the FinancialYear column to choose the required years.
  2. Expand the DisplayStrings column and choose PP-MMM.
  3. Change the Type of the FinancialYear column to Text.
  4. Remove all columns except DatesByMonthID, FinancialYear and PP-MMM.

Download and shape the Accounts data

The Accounts endpoint provides the chart of account items used on the report. It includes both header and detail accounts defined within your MYOB company data file.

Accounts sample data

To shape the Accounts endpoint, apply the following steps:

  1. Expand the ParentAccount column and choose UID.
  2. Rename the following columns:
    UID to AccountUID
    Name to AccountName
    DisplayID to AccountNumber
    ParentAccount.UID to ParentAccountUID
  3. Add the AccountNumberAndName field with the following formula.
[AccountNumber] & " " & [AccountName]
  1. Remove any unnecessary columns keeping only AccountUID, AccountName, AccountNumber, Classification, ParentAccountUID, IsHeader and AccountNumberAndName.

Download and shape the AccountsClassifications data

The AccountsClassifications endpoint provides the group of the classifications. These indicate whether the accounts are used on the Profit and Loss or not.

AccountsClassifications sample data

To shape the AccountsClassifications endpoint, apply the following steps:

  1. Remove any unnecessary columns keeping only Classification and IsProfitLoss

Download and shape the AccountsTotals data

The AccountsTotals endpoint provides the total rows that appear on the report. This includes items such as Income, Cost of Sales, Gross Profit, Expenses, Operating Profit, Net Profit, etc.

We will be creating two Power Queries from this one endpoint:

  • AccountsTotals
  • AccountsTotalsClassifications
AccountsTotals sample data

To shape the AccountsTotals endpoint into two separate tables, apply the following steps:

  1. Add a step at the end and set the formula to Table.Buffer(AccountsTotals_table).
  2. Create a new Power Query by right-clicking on AccountsTotals and choose Reference.
  3. Rename this new table to AccountsTotalsClassifications.
  4. Expand the Classifications column and include both Classification and DrCrSign.
  5. Remove all unnecessary columns keeping only AccountTotalName, Classification and DrCrSign.
AccountsTotalsClassifications sample data.

Download and shape the AccountRegister data

The AccountRegister endpoint provides the values for the report.

AccountRegister sample data.

To shape the AccountRegister endpoint, apply the following steps:

  1. Expand the Account column and include the UID field.
  2. Rename the Account.UID column to AccountUID.
  3. Add the Movement field with the following formula.
[Activity] + [Adjustment] + [YearEndActivity] + [YearEndAdjustment]
  1. Remove any unnecessary columns keeping only AccountUID, DatesByMonthID and Movement.

Add relationships between the data within Power BI

Once all tables are downloaded, it is time to add relationships between them. Relationships allows Power BI to navigate through the data to calculate the required values.

Add the following relationships between the tables. It is recommended to set the Cross Filter Directrion to Both.

  • AccountRegister[AccountUID] field and the Accounts[AccountUID] field.
  • AccountRegister[DatesByMonthID] field and the DatesByMonth[DatesByMonthID] field.
  • Accounts[Classification] field and the AccountsClassifications[Classification] field.
  • AccountsTotalsClassifications[AccountsTotalName] field and the AccountsTotals[AccountsTotalName] field.
  • AccountsTotalsClassifications[Classification] field and the AccountsClassifications[Classification] field.

Create the DAX columns for the account hierarchy

To create the nested level of account hierarchies, we add 5 DAX columns. The first one creates a path that Power BI uses to navigate through the hierarchy. The others pull the actual account number and name for levels 1 to 4.

These DAX columns are added to the Accounts table.

Create the AccountPathID DAX column

The AccountPathID column is an interim column. It calculates the path between the account hierarchy. This is in turn used in the other DAX columns below.

AccountPathID = PATH(Accounts[AccountUID], Accounts[ParentAccountUID])

Create the AccountNumberNameL1 DAX column

The AccountNumberNameL1 column pulls the related the level 1 AccountNumberAndName for all accounts.

AccountNumberNameL1 = 
   LOOKUPVALUE(
      Accounts[AccountNumberAndName], 
      Accounts[AccountUID],       
      PATHITEM(Accounts[AccountPathID], 1)
   )

Create the AccountNumberNameL2 DAX column

The AccountNumberNameL2 column pulls the related the level 2 AccountNumberAndName for all accounts.

AccountNumberNameL2 = 
   LOOKUPVALUE(
      Accounts[AccountNumberAndName], 
      Accounts[AccountUID],       
      PATHITEM(Accounts[AccountPathID], 2)
   )

Create the AccountNumberNameL3 DAX column

The AccountNumberNameL3 column pulls the related the level 3 AccountNumberAndName for all accounts.

AccountNumberNameL3 =    
   LOOKUPVALUE(
      Accounts[AccountNumberAndName], 
      Accounts[AccountUID],       
      PATHITEM(Accounts[AccountPathID], 3)
   )

Create the AccountNumberNameL4 DAX column

The AccountNumberNameL4 column pulls the related the level 4 AccountNumberAndName for all accounts.

AccountNumberNameL4 = 
   LOOKUPVALUE(
      Accounts[AccountNumberAndName], 
      Accounts[AccountUID],       
      PATHITEM(Accounts[AccountPathID], 4)
   )

Create the DAX measures to calculate the correct Profit and Loss values

In order to calculate the correct Profit and Loss values, we create 3 measures.

These measures are used to ensure:

  • Only detail account values are used.
  • Totals are calculated correctly.
  • Totals do not show values for accounts used in their calculation.

Note that while you can create these DAX measures on any table, we recommend adding them to the AccountRegister table.

Create the Movement1_Detail DAX measure

The Movement1_Detail measure calculates the values but only for detail accounts. This ensures values belonging to header accounts are excluded. If these values are not excluded, Power BI would be double or triple counting values.

It is an interim measure that is used as base for other the Movement2_ForTotals.

Movement1_Detail = 
   CALCULATE(
      SUM(AccountRegister[Movement]),
      FILTER(
         Accounts,
         Accounts[IsHeader] = false
      )
   )

Create the Movement2_ForTotals DAX measure

The Movement2_ForTotals measure ensures that total rows (such as Gross Profit and Net Profit) sum up. correctly.

It uses the DrCrSign field to achieve this. This field has a value of 1 or -1 depending on the related account classification.

This ensures, for instance, that Gross Profit is equal to Income less Cost of Sales.

It is an interim measure that is used as base for other the Movement3_Final.

Movement2_ForTotals = 
   SUMX(
      AccountsTotalsClassifications, 
      [Movement1_Details] *  AccountsTotalsClassifications[DrCrSign]
   )

Create the Movement3_Final DAX measure

The Movement3_Final measure hides the breakdown of values for the totals. It ensures that Income shows the breakdown of the related accounts, but Gross Profit does not.

This measure is the final measure used directly on the report.

Movement3_Final = 
   IF(
      SELECTEDVALUE(AccountTotals[IsAccountClassification]) = FALSE
      && ISFILTERED(Accounts) = true,
      BLANK(), [Movement2_ForTotals]
   )

Create the Profit and Loss report using a matrix visual element

Once the data is ready, create the report using the following steps.

  1. Add a Matrix visual element to your dashboard.
  2. Expand the AccountsTotals table and add the AccountTotalName field to the rows.
  3. Expand the Accounts table and add the AccountNumberNameL1, AccountNumberNameL2, AccountNumberNameL3 and AccountNumberNameL4 fields to the rows.
  4. Expand the DatesByMonths table and add the PP-MMM field to the columns.
  5. Expand the AccountRegister table and add the Movement3_Final DAX measure to the values.
  6. Expand the AccountsClassifications table, add the IsProfitLoss field to the Page Filter section and set it to True.

You can also add a slicer to filter by financial year using the following instructions.

  1. Add a Slicer visual element to your dashboard.
  2. Expand the DatesByMonth table and add the FinancialYear field to the slicer field.

Finally, your report is ready to use.

Profit and Loss Report in Power BI using MYOB data and the AccountRegister Endpoint