Profit and Loss Power BI Reports for MYOB

Home / Report Templates / Profit and Loss Power BI Reports for MYOB

The Profit and Loss Power BI Reports for MYOB is a simple dashboard that provides a series of reports to analyse your Profit and Loss statements in Power BI.

The dashboard includes:

  • Monthly Profit and Loss Report
  • Monthly Profit and Loss Forecast Report and Chart
  • Profit and Loss Statement
  • Budget Analysis Report
  • Budget Variance By Month Report

This report can also consolidate data from multiple MYOB companies (data files) when using the Consolidation OData Feed.

  • Budget Analysis Report for MYOB
    Budget Analysis Report for MYOB
  • Profit and Loss Forecast Chart for MYOB
    Profit and Loss Forecast Chart for MYOB
  • Profit and Loss Report for MYOB
    Profit and Loss Report for MYOB
  • Profit and Loss Statement for MYOB
    Profit and Loss Statement for MYOB
  • Budget Variance by Month Report for MYOB
    Budget Variance by Month Report 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

Monthly Profit and Loss Report

The Monthly Profit and Loss Report is a simple report that displays Accounts down the page and Months across the page.

Monthly Profit and Loss Forecast

The Monthly Profit and Loss Forecast is a simple report that displays Accounts down the page and Months across the page.

It calculates Forecast values by using a parameter entered in Power BI to control the cut off between actuals and budget values.

Monthly Profit and Loss Forecast Chart

The Monthly Profit and Loss Forecast Chart uses the same data as the Monthly Profit and Loss Forecast but display it as a chart instead.

Profit and Loss Statement

The Profit and Loss statement displays Month-to-Date (MTD) and Year-to-Date (YTD) actual values for your accounts and includes a % of Sales column.

Budget Analysis

The Budget Analysis report compares Actual vs Budgets and includes Variance and Variance %. Values are both included Month-to-Date (MTD) and Year-to-Date (YTD).

Budget Variance by Month

The Budget Variance by Month is similar to the Budget Analysis report but displays months across the page.

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

Currently, we only provide a single version of this report which provides company-wide Profit and Loss values. If interested in job-based or cost-centre-based reporting, let us know.

You can download the Power BI template from the following link.

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. Enter the Date Forecast.
    This is used as the cut off point where the forecast report and chart uses your budget data to complete the forecast.
  4. Select the Budget Source from the following values:
    Choosing Budget will pull budget values from MYOB.
    Choosing Actual Last Year will use last years actual values as a pseudo-budget.
    Choosing None will not calculate budget values.
  5. Select the Budget Variance from the following values:
    Choosing Standard will calculate the variance as Actual less Budget.
    Choosing Impact will calculate the variance differently for income and expense accounts so that a positive value is positive to your business.
    Choosing Remaining will calculate the variance as Budget less Actual and is useful for Not-For-Profits (NFP) that receive grants and must spend to a budget.
  6. Select the Budget Variance Percent from the following values:
    Choosing Actual will use actual values as the numerator for the division.
    Choosing Variance will use variance values as the the numerator for the division.
    In both case, the denominator is the budget values.
  7. Select whether to Download Budget Data.
    Choosing No will speed the refresh process.
  8. Click the Refresh button and wait for the data to download.

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.