Create a Profit and Loss Report for Xero in Power BI using OdataLink

This article provides the steps necessary to create a Profit and Loss Report for Xero in Power BI using OdataLink.

The intention is to produce a Profit and Loss report that provides 12 months of data.

This article uses endpoints downloaded from Xero 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 Xero 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 Profit and Loss report in Power BI using Xero data

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

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

Downloading the required data from Xero

To start off, you need to download 6 endpoints.

• Accounts
• AccountsTypes
• AccountsTotals
• DataFiles
• DatesByMonth
• ProfitAndLossMultiPeriodTable

Download and shape the Accounts data

The Accounts endpoint provides the detailed chart of account items defined within Xero. On the report, they are used as the 3rd level in the hierarchy.

To shape the Accounts endpoint, apply the following steps:

1. Expand the DataFile column to include the DataFileID.
2. Remove any unnecessary columns keeping only AccountID, Name, Code, Type and Class.

Download and shape the AccountsTypes data

The AccountsTypes endpoint provides the list of types of accounts that can be defined in Xero. On the report, they are used as the 2nd level in the hierarchy.

They are important as they allow DAX to aggregate data from the Accounts to the AccountsTotals.

To shape the AccountsTypes endpoint, apply the following steps:

1. Remove any unnecessary columns keeping only AccountType and AccountTypeName

Download and shape the AccountsTotals data

The AccountsTotals endpoint provides the total rows that appear on the report. This is the first level in the indented hierarchy you see on the report.

This endpoint 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
• AccountsTotalsTypes

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 AccountsTotalsTypes.
4. Expand the AccountsTypes column and include both AccountType and DrCrSign.
5. Remove all unnecessary columns keeping only AccountTotal, AccountType and DrCrSign.

Download and shape the DataFiles data

The DataFiles endpoint provides the list of data files when downloading from the Consolidation OData feed. While this endpoint is only useful when pulling data from multiple Xero Organisation, it is good practice to include it as it can allow your reports to scale up.

To shape the DataFiles endpoint, apply the following steps:

1. Remove all columns except DataFileID, and DataFileName.

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.

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 MMM-YYYY.
3. Change the Type of the FinancialYear column to Text.
4. Transform the LastDate column into a Date.
5. Rename the LastDate column to Date.
6. Remove all columns except DatesByMonthID, FinancialYear, Date and MMM-YYYY.

Download and shape the ProfitAndLossMultiPeriodTable data

The ProfitAndLossMultiPeriodTable endpoint provides the values for the report.

This endpoint is a function that can be invoked with specific parameters to control the data it downloads. To shape the endpoint, apply the following steps:

1. Select the Invoke step and click on the gear icon to set values for the parameters.
2. Remove any unecessary columns keeping only the DataFileID, ToDate, AccountID and Amount columns.
3. Filter the AccountID column and remove null.
4. Filter the Amount column and remove zeroes (0).
5. Rename the ToDate column to Date.

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. It essentially sets up the logical path to calculate values when including fields from other tables.

Add the following relationships between the tables. It is recommended to set the Cross Filter Direction to Both for all account related tables.

• AccountsTotals[AccountTotal] field and the AccountsTotalTypes[AccountTotal] field.
• AccountsTotalsTypes[AccountType] field and the AccountsTypes[AccountType] field.
• AccountsTypes[AccountType] field and the Accounts[Type] field.
• ProfitAndLossMultiPeriodTable[AccountID] field and the Accounts[AccountID] field.
• ProfitAndLossMultiPeriodTable[DataFileID] field and the DataFiles[DataFileID] field.
• ProfitAndLossMultiPeriodTable[Date] field and the DatesByMonth[Date] field.

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

In order to calculate the correct Profit and Loss values, we need create a Movement measure. This can be a single measure, or you can break it down into 3 different measures.

These measures are used to ensure:

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

Without defining these measures, Power BI would calculate incorrect values for totals such as Gross Profit and Net Profit. Instead of calculating Gross Profit as Income less Cost of Sales, it would simply sum up all related accounts.

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

Create the Movement1_Detail DAX measure

The Movement1_Detail measure is a base measure that converts the amount value into a measure.

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

Movement1_Detail = 
   CALCULATE(SUM(ProfitAndLossMultiPeriodTable[Amount]))

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 of the AccountsTotalsTypes table to achieve this. This field has a value of 1 or -1 depending on the related account type.

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(
      AccountsTotalsTypes, 
      [Movement1_Details] *  AccountsTotalsTypes[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(AccountsTotals[IsAccountClassification]) = FALSE
      && (ISFILTERED(Accounts) = true || ISFILTERED(AccountsTypes) = true),
      BLANK(), [Movement2_ForTotals]
   )

Alternatively, create a single Movement DAX measure

Alternatively, you can create a single Movement DAX measure that encompasses all the business logic. This is our preferred approach as it keeps the number of measures small.

Movement = 
   IF(
      SELECTEDVALUE(AccountsTotals[IsAccountClassification]) = FALSE
      && (ISFILTERED(Accounts) = true || ISFILTERED(AccountsTypes) = true),
      BLANK(), 
      SUMX(
            AccountsTotalsTypes,
            CALCULATE(SUM(ProfitAndLossMultiPeriodTable[Amount]))
            * AccountsTotalsTypes[DrCrSign]
      )
   )

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. Expense the AccountsTypes table and add the AccountTypeName field to the rows.
4. Expand the Accounts table and add Name fields to the rows.
5. Expand the DatesByMonths table and add the MMM-YYYY field to the columns.
6. Expand the ProfitAndLossMultiPeriodTable table and add the Movement3_Final or Movement DAX measure to the values.

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.

You can also add a slicer to filter by data file using the following instructions.

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

Finally, your report is ready to use.