Skip to content

Scorecard Reporting Xero Zoho

This page documents how the Excel scorecard report is currently constructed. The P&L is a workbook-side report built from the flattened Xero Profit and Loss export, not a static copy of Xero's screen. Sales movement reporting is fed by the scorecard CRM exports for leads and deals.

Workbook Files

  • pbi/scorecard-excel/pepit_scorecard_init.xlsx is the workbook used to carry the refreshed Power Query source and query shape.
  • pbi/scorecard-excel/pepit_scorecard_dev.xlsx is the development workbook used for report layout work before publishing to SharePoint.
  • The Power Query table is named xero_profit_and_loss.
  • CRM staging tables include leads, deals, msp_client_size_calc, and all_clients.
  • The report sheet is named P&L.

The P&L sheet should stay formula-driven. Do not paste static values into the month columns as the final report mechanism, because those values will not follow the next Power Query refresh.

Xero Profit And Loss Query

The query source lives at:

pbi/scorecard-excel/queries/xero_profit_and_loss.m

It calls the backend route:

/scorecard/xero-profit-and-loss.csv

Current query parameters are:

  • ReportEndDate: the month end date to report to, for example #date(2026, 7, 31).
  • MonthCount: 36.
  • MonthsPerRequest: 12.
  • RequestCount: the number of twelve-month windows needed to cover MonthCount, currently 3.
  • FromDate: the first day of each request window's end month.
  • ToDate: the end date for each request window.
  • Periods: MonthsPerRequest - 1, currently 11.
  • Timeframe: MONTH.
  • StandardLayout: true.
  • PaymentsOnly: false.

This appends three twelve-month Xero report windows to produce the current month plus the previous thirty-five months. Do not replace this with a single financial-year start FromDate. With this Xero endpoint, that can produce cumulative period columns instead of discrete month columns.

The query keeps Xero's original report structure and then adds workbook helper columns:

  • period_end_date: month-end date for each Xero value column.
  • report_month_index: month position relative to ReportEndDate, where 1 is the latest month.
  • period_label: display label such as Jul 2026.
  • cell_value_number: numeric version of cell_value.
  • line_key: section_path and row_title joined with |.
  • is_month_value: true only for non-header month value cells.

The query also retains Xero layout fields such as row_order, section_title, section_path, row_type, row_title, cell_index, and column_title.

P&L Sheet Layout

The P&L sheet is a fixed report layout with hidden helper cells.

Columns:

  • A: hidden line_key.
  • B: hidden row_type.
  • C: hidden row_order.
  • D: visible report line label.
  • E:AN: thirty-six monthly value columns.

Rows:

  • Row 2: report title.
  • Row 4: entity name.
  • Row 5: report period text, based on the latest month date in row 7.
  • Row 6: hidden month index values from 1 to 36.
  • Row 7: hidden month-end dates.
  • Row 8: visible month labels.
  • Row 9 onwards: P&L sections, detail lines, and totals.

Row 7 starts with the latest month-end date in the source table, then rolls back one month at a time. For example, E7 uses:

=MAX(xero_profit_and_loss[period_end_date])

F7:AN7 use the previous month-end date from the cell to the left. Row 8 points at row 7 and is formatted as mmm yyyy.

Each numeric report cell uses SUMIFS against the staging table. For example, the first detail row in column E uses this pattern:

=IFERROR(SUMIFS(xero_profit_and_loss[cell_value_number],xero_profit_and_loss[line_key],$A10,xero_profit_and_loss[row_type],$B10,xero_profit_and_loss[is_month_value],TRUE,xero_profit_and_loss[period_end_date],E$7),0)

The formula deliberately matches on hidden keys rather than the visible label. That keeps labels editable for presentation while the calculation still follows the Xero row identity.

Section rows do not calculate values. Summary rows use the same SUMIFS pattern as detail rows, because Xero supplies summary rows such as gross profit and net profit in the flattened report.

Because summary rows come from Xero, displayed totals can remain correct even when some detail account lines are not shown on the P&L sheet. Missing detail lines therefore mainly affect transparency and reconciliation: the visible detail rows may not add up to the displayed Xero total unless every relevant line is included.

Managing Report Lines

The report layout is seeded from Xero's row order. Each visible line should have the matching hidden line_key and row_type.

The workbook includes a P&L Line Check sheet to make this maintenance visible. It compares unique source line_key values from xero_profit_and_loss against the hidden line_key column on P&L. After refreshing the Xero P&L query, filter Report status to Missing to see source lines that need to be added to the report layout.

When Xero adds, removes, or renames accounts:

  1. Refresh xero_profit_and_loss.
  2. Check P&L Line Check for missing source lines.
  3. Add or update the corresponding row on P&L.
  4. Copy the existing month formulas across the row.
  5. Confirm the hidden line_key and row_type match the staging table.

If a line's section_path or row_title changes in Xero, the old line_key will no longer match and the report line will show zero. Update the hidden line_key or rebuild that part of the layout from the refreshed table.

The visible label in column D does not have to match Xero exactly. It can be renamed for scorecard presentation as long as the hidden keys remain correct.

Desktop Refresh Checklist

Before using the report:

  1. Set ReportEndDate in the Power Query source.
  2. Refresh xero_profit_and_loss.
  3. Confirm the staging table has thirty-six months of data after refresh.
  4. Confirm P&L!E7:AN7 contains date formulas, not blanks.
  5. Confirm P&L!E8:AN8 displays month labels.
  6. Confirm the value cells contain formulas, not static pasted values.
  7. Recalculate the workbook if Excel has not calculated after refresh.

If month labels show #NAME?, check the formula in row 7. The layout should use ordinary MAX, DATE, YEAR, and MONTH formulas, not MAXIFS, because some Excel environments used for this workbook do not support MAXIFS.

SharePoint Refresh

The SharePoint-hosted workbook can be refreshed from Excel for the web when the Power Query data sources and workbook objects are valid.

Each user who refreshes in Excel for the web may be prompted to set data source credentials for https://pepit.fxbn.dev. Use Basic auth with the shared Excel/Power BI credentials configured in the backend as EXCEL_BASIC_USER and EXCEL_BASIC_PASS.

Those Basic credentials are not a personal Zoho or Xero login. They are the middleware report credentials. In Excel for the web, the saved data source credential should be treated as belonging to the Microsoft user who configured it. Other users can see data already saved in the workbook, but should expect to configure their own data source credentials before they can refresh.

Three-Year Layout

The current workbook is built for thirty-six months. The query appends additional twelve-month Xero windows into the same staging-table shape, keeping period_end_date, report_month_index, line_key, row_type, and is_month_value consistent.

The P&L layout extends the same SUMIFS pattern horizontally across all thirty-six month columns.

Leads And Sales Movement

The scorecard should use leads for lead movement reporting. The Power Query source lives at:

pbi/scorecard-excel/queries/leads.m

It calls the backend route:

/scorecard/leads.csv

Zoho CRM removes converted leads from the normal Leads module view, so this route deliberately fetches Leads with the Zoho CRM records API parameter converted=both. Zoho documents this parameter on the Get Records API for Leads, with false returning only non-converted records, true returning only converted records, and both returning all records. The export also requests Zoho's conversion fields Converted__s and Converted_Date_Time.

The Leads staging table includes:

  • created_month_start: first day of the month from Created_Time.
  • is_converted: true when Zoho marks the lead as converted.
  • converted_month_start: first day of the month from Converted_Date_Time.

Use created_month_start to count new leads by month. Use converted_month_start with is_converted = TRUE to count converted leads by month. This keeps lead creation and lead conversion as separate events; a lead created in May and converted in June should count once in May's new-lead count and once in June's converted-lead count.

The current deals export already carries the fields needed for deal movement reporting:

  • created_time: count deals created by month.
  • stage: classify whether a deal is won, lost, or still open.
  • closing_date: count won or lost deals in the month they closed.

For the workbook, count deal creation from created_time, then count won and lost deals from closing_date filtered by the relevant CRM stage values. Keep the won/lost stage mapping in the workbook or a small helper table until the exact stage labels are confirmed from CRM.