FAQ: How to Generate a Monthly Profit and Loss Report in Xero (or Make It Dynamic)?

A Profit and Loss (P&L) report shows how much your business has earned and spent over a period. It tracks your revenue, expenses, and net profit. You can use it to see trends, compare performance across months or years, and make informed decisions about costs and growth.

Below, you’ll learn step by step how to:

• Generate a P&L for a completed year with a monthly breakdown( taking 2024 year)
• Generate reports mid-year, even with Xero’s date restrictions
• Make reports dynamic so you don’t have to update them manually
• How to Build a report to compare this year and last year side by side without having to manually update in the future.

Generate a P&L for a completed year with a monthly breakdown

( taking 2024 year)

Goal: Show each month of 2024 as a separate column in your report.

How to set it up:

Start Date: 2024-12-01

End Date: 2024-12-31

Number of periods to compare: 11

Period: Month

How it works:

Xero will create the December report first and then look back 11 months. This gives you totals for January to December 2024 in one go.

Tip:

You only need to adjust these dates once a year.

How to generate a report for the current year

if today was June 18th, 2025

If you are mid year, say today is June 18th, and need to be able to see a monthly breakdown of a your Profit & Loss report, you can set this up this way:

• Start Date: 2025-07-01
• End Date: 2025-07-31
• Number of periods to compare: 6
• Period: Month

Notice: Even though today is June 18th, 2025, we set the End Date to the end of July.

This will display the report for the last month of the desired period and add 6 previous months to compare values, which is exactly what we need to see.

Make reports dynamic so you don’t have to update them manually:

💡 A better alternative is to use our Macros for date ranges, especially if you want to automate rolling P&L comparisons without editing the flow each time.

Lets say you would like to have a Profit &loss report that dynamically calculates this data for your each year without having to manually change the dates from this:

• Start Date: 2024-12-01——to ——-> Start Date: 2025-12-01
• End Date: 2024-12-31———to ——->End Date: 2025-12-31
• Number of periods to compare: 11
• Period: Month

To avoid this, you can use our macros to dynamically adjust the date range every time the flow runs. That way, you always get fresh, up-to-date data without needing to edit the settings manually.

• Start Date: {{today.format(YYYY-12-01)}}
• End Date: {{today.format(YYYY-12-31)}}
• Number of periods to compare: 11
• Period: Month

How to clean up the raw data

The setup above will provide you a breakdown that can be then organized in our Preview and Transformation steps by following these steps:

1. Hide unnecessary columns like so:

2. Rename columns to match the Xero breakdowns like so:

3. Reorganize columns by dragging-and-dropping them to you prefer setup from Jan to December, as shown below:

Final result can look like this:

How to Build a report to compare this year and last year side by side:

Dynamic approach can be helpful as well If you'd like to compare Last year vs This year side by side, and not have to manually change dates:

Do this manually, you can acheive this by setting up two data sources in the same data flow:

1. Data source 1- get this year's Profit & Loss for all month:
   1. Start Date: {{today.format(YYYY-12-01)}}
   2. End Date: {{today.format(YYYY-12-31)}}
   3. Number of periods to compare: 11
   4. Period: Month

Once done, duplicate the source here:

2. In the duplicated data source 2 - You can extract past year’s P&L for all months getting all dates, by using this parameters:

• Start Date: {{1yearago.format(YYYY-12-01)}}
• End Date:{{1yearago.format(YYYY-12-31)}}
• Number of periods to compare: 11
• Period: Month 

Then you'll have two data sources like so:

Once the data is loaded, you can combine leverage our Preview and Transformation features to keep only the columns for months you need and combine the data if you prefer. (example here)

How to Combine This Year and Last Year’s P&L Data for Year-over-Year Comparison

If you want to compare this year’s monthly P&L data with last year’s side by side, here’s what you need to know:

1. Step 1: Choose a report format that fits your needs:

   Our Xero connector lets you choose between two report structures, as shown below:

   1. Xero-style format: returns one column per month (like 31 Jan 25 , 28 Feb 25 )
   2. Date-by-row format: returns one row per date (with Date , Account , Value as fields)

For comparing this year and last year using the Append option in Transformations, you should switch to Date-by-row format.

If you use Xero-style format, like we done so far, each month includes the year in the column name, so 31 Jan 24 and 31 Jan 25 will be treated as separate columns.

Step 2: Set up your two data sources

One for each year, both using the Date-by-row structure:

Step 3: Add a “Year” column

Still inside each individual source, go to Formula and create a new column as shown below:

DATETIME_FORMAT(date, "YYYY")

This makes it possible to compare months across different years after appending.

Step 4: Append the two years

Now that both data sets are cleaned and structured the same way, use the Append tool to combine them into one single dataset.

This lets you filter or group by Year and category_level_2 , and compare monthly P&L data across years.