Create a Profit and Loss Actual vs Budget vs Forecast dashboard in Power BI using Xero Data and DAX

This article provides an example to create a Profit and Loss Actual vs Budget vs Forecast dashboard in Power BI using Xero data and DAX.

The intention is to produce a chart which includes Profit and Loss values up to a specific month and follows up with budget values. It includes a line that shows the forecast data.

This article uses Profit and Loss as well as Budget data 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. This article also makes use of the YTD measures.

About OdataLink

OdataLink is a powerful tool that allows you to download many endpoints of data from Xero as OData Feeds directly into Power BI or Excel without the need for any third-party software. For more information on OdataLink, see odatalink.com.

Create a P&L Actual vs Budget vs Forecast dashboard in Power BI using Xero Data and DAX

There are four parts to create the Profit and Loss Actual vs Budget vs Forecast dashboard.

  1. Download the required data from Xero
  2. Add relationships between the data in Power BI
  3. Add DAX measures to create the necessary values
  4. Create the chart using the DAX measures.

Downloading the required data from Xero

Using OdataLink, we download three tables of information from Xero into Power BI:

Download and shape ProfitAndLossByMonth data

The ProfitAndLossByMonth Xero endpoint provides the Actual values for the report.

It is a function that accepts a financial year as input and returns 12 months of values for each account including some totals (such as Net Profit).

To shape the ProfitAndLossByMonth endpoint to create the dashboard, we apply the following steps:

  1. Invoke the endpoint with the correct financial year (2022 in our example).
  2. Expand the Lines column to include the AccountID, AccountName and Amounts columns.
  3. Expand the Amounts column to include the Date and Amount columns.
  4. Filter the Amount column to remove zero values.
  5. Remove any unnecessary columns.

Download and shape BudgetSummaryByMonth data

The BudgetSummaryByMonth Xero endpoint provides the Budget values for the report.

It is a function that accepts a financial year as input and returns 12 months of values for each account including some totals (such as Net Profit).

To shape the BudgetSummaryByMonth endpoint to create the dashboard, we apply the following steps:

  1. Invoke the endpoint with the correct financial year (2022 in our example).
  2. Expand the Lines column to include the AccountID, AccountName and Amounts columns.
  3. Expand the Amounts column to include the Date and Amount columns.
  4. Filter the Amount column to remove zero values.
  5. Remove any unnecessary columns.

Download and shape Dates data

The Dates endpoint provides a mechanism to:

  • chart the months across the actual chart.
  • create the Year-To-Date measures
  • control the toggle between the Actual and Forecast dates

To control the later toggle, we add a parameter to power query named varForecastDate and set it to a default value. We use this parameter within a Custom Field named IsActualForecast to return either the word Actual or Forecast. The DAX measures use this information to calculate the necessary values.

To shape the Dates endpoint to create the dashboard, we apply the following steps:

  1. Filter the FinancialYear column for the required year (2022 in our example).
  2. Expand the DisplayStrings column to include the MMM-YYYY column.
  3. Add the IsActualForecast column with the following formula.
(if [Date] >= varForecastDate then "Forecast" else "Actual")
  1. Remove any unnecessary columns.

Add relationships between the data within Power BI

Finally, in order for the dashboard to work, we add the following relationships between the endpoints.

  • ProfitAndLossByMonth[Date] field and the Dates[Date] field.
  • BudgetSummaryByMonth[Date] field and the Dates[Date] field.

Create the DAX measures to Forecast

In order to create the Actual vs Budget vs Forecast dashboard, we create 8 measures. Note that you can create these DAX measures on any table.

Create an ActualAmount DAX measure

The ActualAmount measure calculates the actual Month-To-Date (MTD) amount. It is an interim measure that is used as base for other the ActualAmountYTD and ForecastAmount measures.

ActualAmount = CALCULATE(SUM(ProfitAndLossByMonth[Amount]))

Create an ActualAmountYTD DAX measure

The ActualAmountYTD measure calculates the Year-To-Date (YTD) amount for actual values. It is an interim measure that is used as base for the ActualAmountYTDCapped measure. It uses the ActualAmount as input.

ActualAmountYTD = TOTALYTD(ProfitAndLossByMonth[ActualAmount], Dates[Date] , ALL(Dates), "03/31")

Create an ActualAmountYTDCapped DAX measure

The ActualAmountYTDCapped is the measure that will be included on the chart to represent the actual value.

It uses the ActualAmountYTD measure as input but is capped (restricted) to only include values for Dates whose IsActualForecast value is set to Actual. This is to ensure it is only charted for those specific months.

ActualAmountYTDCapped = 
   CALCULATE(
      ProfitAndLossByMonth[ActualAmountYTD],
      FILTER(
         Dates,
         Dates[IsActualForecast] = "Actual"
      )
   )

Create a BudgetAmount DAX measure

The BudgetAmount calculates the budget Month-To-Date (MTD) amount. It is an interim measure that is used as base for the BudgetAmountYTD and ForecastAmount measures.

BudgetAmount = CALCULATE(SUM(BudgetSummaryByMonth[Amount]))

Create a BudgetAmountYTD DAX measure

The BudgetAmountYTD measure calculates the Year-To-Date (YTD) amount for budget values. It is an interim measure that is used as base for the BudgetAmountYTDCapped measure. It uses the BudgetAmount as input.

BudgetAmountYTD = TOTALYTD(BudgetSummaryByMonth[BudgetAmount], Dates[Date], ALL(Dates), "03/31")

Create a BudgetAmountYTDCapped DAX measure

The BudgetAmountYTDCapped is the measure that will be included on the chart to represent the budget value.

It uses the BudgetAmountYTD measure as input but is capped (restricted) to only include values for Dates whose IsActualForecast value is set to Forecast. This is to ensure it is only charted for those specific months.

BudgetAmountYTDCapped = 
   CALCULATE(
      BudgetSummaryByMonth[BudgetAmountYTD],
      FILTER(
         Dates,
         Dates[IsActualForecast] = "Forecast"
      )
   )

Create a ForecastAmount DAX measure

The ForecastAmount measure either uses the ActualAmount measure or the BudgetAmount measure. It toggles between these values based on the IsActualForecast column of the Dates table.

This interim measure is used as base for the ForecastAmountYTD measure.

It uses the ActualAmount and BudgetAmount as input.

ForecastAmount = 
   CALCULATE(ProfitAndLossByMonth[ActualAmount], FILTER(Dates, Dates[IsActualForecast] = "Actual"))
   + CALCULATE(BudgetSummaryByMonth[BudgetAmount], FILTER(Dates, Dates[IsActualForecast] = "Forecast"))

Create a ForecastAmountYTD DAX measure

The ForecastAmountYTD calculates the Year-To-Date (YTD) amount for the forecast values. It uses the ForecastAmount as input.

ForecastAmountYTD = TOTALYTD(ProfitAndLossByMonth[ForecastAmount], Dates[Date], ALL(Dates), "03/31")

Create the Actual vs Budget vs Forecast chart in Power BI

Once all measure are created, you can create the chart by adding the following fields.

  • Add the MMM-YYYY field of the Dates table to the Shared Axis.
  • Add the ActualAmountYTDCapped measure to the Column Values section and rename it to Actual.
  • Add the BudgetAmountYTDCapped measure to the Column Values section and rename it to Budget.
  • Add the ForecastAmountYTD measure to the Line Values section and rename it to Forecast.
  • Add the AccountName field of the ProfitAndLossByMonth to the Filter on Visuals and choose only Net Profit.
  • Add the AccountName field of the BudgetSummaryByMonth to the Filter on Visuals and choose only Net Profit.