Skip to main content

Prebuilt Measures in Connectorly Templates

Detailed explanations of the core financial, customer, and performance KPIs included in our Power BI reports.

Laszlo Molnar avatar
Written by Laszlo Molnar
Updated yesterday

This article provides a complete reference of the predefined measures used across our Connectorly templates.

Each measure includes the original DAX formula along with a clear explanation of its purpose and calculation logic.

Use this guide to better understand how key financial, revenue, customer, and SaaS KPIs are structured within the model.

Table name

Measure name

Measure DAX

Description

Xero bank transactions

Balance Amount (Currency) RT

Balance Amount (Currency) RT = CALCULATE(sum('xero bank_transactions'[Amount (Currency)]),filter(all('xero dates'), 'xero dates'[Date] <= max('xero dates'[Date])))

This measure calculates a running total (balance) of bank transaction amounts in transaction currency. It removes existing date filters and then sums all transactions up to the current date in the visual. As a result, it shows the bank balance as of each date, rather than just the activity on that day.

Xero bank transactions

Balance Amount (RC) RT

Balance Amount (RC) RT = CALCULATE(sum('xero bank_transactions'[Amount (RC)]),filter(all('xero dates'), 'xero dates'[Date] <= max('xero dates'[Date])))

This measure calculates a running balance in reporting currency (RC) based on bank transactions.
It ignores existing date filters and sums all transaction amounts up to the current date in the visual.
The result shows the bank balance as of each date, expressed in the selected reporting currency.

Xero bank transactions

Balance Amount RT

Balance Amount RT = CALCULATE(sum('xero bank_transactions'[Amount]),filter(all('xero dates'), 'xero dates'[Date] <= max('xero dates'[Date])))

This measure calculates a running balance using the raw bank transaction amount.
It removes existing date filters and sums all transactions up to the current date in the visual.
The result shows the bank balance as of each date, using the original transaction amount.

Xero became customer

Customer Acquisition Cost in Period

Customer Acquisition Cost in Period = 'xero journals'[Marketing Spend (RC)] / 'xero contacts'[Customers Won]

This measure calculates Customer Acquisition Cost (CAC) for the current filter context by dividing Marketing Spend (Reporting Currency) by the number of Customers Won.
It returns the average acquisition cost per customer for the selected period (based on the report’s date and other filters).

Xero became customer

LTV / CAC 

LTV / CAC = 'xero invoices'[Customer LTV] / 'xero became customer'[Customer Acquisition Cost in Period]

This measure calculates the LTV / CAC ratio by dividing Customer Lifetime Value (LTV) by Customer Acquisition Cost (CAC).
It shows how much lifetime revenue a customer generates relative to the cost of acquiring them, within the current filter context.

Xero budgets

Budget Variance (RC)

Budget Variance (RC) = sum('xero journals'[Net Amount (RC)]) - sum('xero budgets'[Net Amount (RC)])

This measure calculates the budget variance in reporting currency (RC) by subtracting the budgeted amount from the actual net amount.
A positive result indicates actuals above budget, while a negative result indicates underperformance versus budget for the selected period.

Xero budgets

Budget Variance % 

Budget Variance % = (sum('xero journals'[Net Amount (RC)]) - sum('xero budgets'[Net Amount (RC)])) / sum('xero budgets'[Net Amount (RC)])

This measure calculates the budget variance percentage by comparing actual net amounts to the budgeted amounts in reporting currency.
It expresses the variance as a percentage of the budget, showing how far actual performance is above or below budget for the selected period.

Xero budgets

Budget Variance Colour 

Budget Variance Colour = "RED"

This measure returns a constant value of “RED”, typically used to control conditional formatting in visuals.
It allows report designers to apply a consistent colour when a specific formatting rule is required.

Xero budgets

Budget Variance Is Good

Budget Variance Is Good = (sumx(filter('xero journals', RELATED('xero accounts'[Class]) = "REVENUE"), 'xero journals'[Net Amount (RC)]) - sumx(filter('xero budgets', RELATED('xero accounts'[Class]) = "REVENUE"), 'xero budgets'[Net Amount (RC)])) + (sumx(filter('xero journals', RELATED('xero accounts'[Class]) = "EXPENSE"), 'xero journals'[Net Amount (RC)]) - sumx(filter('xero budgets', RELATED('xero accounts'[Class]) = "EXPENSE"), 'xero budgets'[Net Amount (RC)]))

This measure calculates the overall budget variance by adding revenue variance (actual revenue minus budgeted revenue) and expense variance (actual expenses minus budgeted expenses), using the account Class to split rows into REVENUE and EXPENSE.
It’s intended to produce a single “is performance good?” style variance figure for the selected period by combining both sides of the P&L.

Xero budgets

Budgeted FY Revenue

Budgeted FY Revenue = 
VAR this_fy_start = calculate(min('xero dates'[Fiscal Year Start]), FILTER(ALL('xero dates'), 'xero dates'[Date] = TODAY()))
    VAR this_fy_end = calculate(min('xero dates'[Fiscal Year End]), FILTER(ALL('xero dates'), 'xero dates'[Date] = TODAY()))
VAR revenue_in_period = calculate(SUMX(
FILTER(('xero budgets'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero budgets'[Budget Date] >= this_fy_start  && 'xero budgets'[Budget Date] <= this_fy_end),
[Net Amount (RC)])
)
return revenue_in_period

This measure returns the total budgeted revenue (RC) for the current fiscal year, based on the fiscal year start/end dates that contain TODAY().
It filters the budgets table to Revenue-class accounts and sums budget amounts where the budget date falls between the fiscal year start and end.

Xero budgets

Budgeted FY YTD Revenue

Budgeted FY YTD Revenue = 
VAR this_fy_start = min('xero dates'[Fiscal Year Start])
VAR budgeted_revenue_in_period = calculate(SUMX(
FILTER(('xero budgets'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero budgets'[Budget Date] >= this_fy_start  && 'xero budgets'[Budget Date] <= TODAY()),
[Net Amount (RC)]))
RETURN budgeted_revenue_in_period

This measure calculates year-to-date (YTD) budgeted revenue in reporting currency (RC) from the start of the current fiscal year up to TODAY().
It filters the budgets table to Revenue accounts and sums budget amounts where the budget date falls between the fiscal year start and today.

Xero budgets

Budgeted Revenue

Budgeted Revenue = calculate(SUM('xero budgets'[Net Amount (RC)]), FILTER ('xero accounts', 'xero accounts'[Class] = "Revenue"))

This measure calculates the total budgeted revenue in reporting currency (RC).
It sums budget amounts from the budgets table, filtered to Revenue-class accounts, within the current report context.

Xero budgets

Budgets Net Amount (RC) ABS

Budgets Net Amount (RC) ABS = (CALCULATE(sum('xero budgets'[Net Amount (RC)]),KEEPFILTERS('xero accounts'[Class] = "Expense")) * -1) + (CALCULATE(sum('xero budgets'[Net Amount (RC)]),KEEPFILTERS('xero accounts'[Class] = "Revenue")))

This measure returns a budget total where expenses are converted to positive values (by multiplying Expense budgets by -1) while Revenue budgets remain unchanged.
It applies the account Class filters (Expense vs Revenue) using KEEPFILTERS, then combines the two results into a single “absolute” budget amount in reporting currency (RC) for the current filter context.

Xero budgets

This Month Budget Revenue

This Month Budget Revenue = 
VAR month_start = calculate(min('xero dates'[Fiscal Month Start]), FILTER('xero dates', 'xero dates'[Date] = TODAY()))
VAR month_end = calculate(min('xero dates'[Fiscal Month End]), FILTER('xero dates', 'xero dates'[Date] = TODAY()))
VAR revenue_in_period = calculate(SUMX(
FILTER(('xero budgets'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero budgets'[Budget Date] >= month_start  && 'xero budgets'[Budget Date] <= month_end ),
[Net Amount (RC)]))
RETURN revenue_in_period

This measure returns the budgeted revenue (RC) for the current fiscal month, using the fiscal month start and end dates that contain TODAY().
It filters the budgets table to Revenue accounts and sums budget amounts where the budget date falls between the current fiscal month start and end.

Xero contacts

Customer Churn

Customer Churn =     
VAR beginning_of_time = Date(1900,1,1)
VAR first_date_in_period = EOMONTH(min('xero dates'[Date]),-1)+1
VAR last_date_in_period = EOMONTH(max('xero dates'[Date]),0)    
VAR start_of_this_month = EOMONTH(TODAY(),-1)+1    
VAR end_of_this_month = EOMONTH(TODAY(),0)    
VAR end_of_previous_period = first_date_in_period - 1     
VAR consider_customer_lost_after_months = CALCULATE(MIN('Report Settings'[Value]), FILTER(ALL('Report Settings'), 'Report Settings'[Setting] = "Customer lost after months"))   
VAR todays_latest_customer_loss_date = EOMONTH(end_of_this_month,-consider_customer_lost_after_months-1)+1   
VAR period_start_customer_loss_date = if(end_of_previous_period > end_of_this_month, todays_latest_customer_loss_date, EOMONTH(end_of_previous_period,-consider_customer_lost_after_months-1)+1)VAR latest_customer_loss_date = IF(last_date_in_period > todays_latest_customer_loss_date, todays_latest_customer_loss_date, EOMONTH(last_date_in_period,-consider_customer_lost_after_months))   
VAR earliest_customer_loss_date = IF(first_date_in_period > todays_latest_customer_loss_date, todays_latest_customer_loss_date + 1, EOMONTH(first_date_in_period,-(consider_customer_lost_after_months + 1))+1)    
VAR latest_date_to_consider = IF(last_date_in_period >= end_of_this_month, end_of_this_month, last_date_in_period)    
VAR earliest_date_to_consider = IF(first_date_in_period >= end_of_this_month, end_of_this_month, first_date_in_period)    
VAR customers_lost_in_period =  CALCULATE(COUNTROWS('xero contacts'), ALL(), FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] <= latest_customer_loss_date && 'xero contacts'[Last Sales Activity] >= earliest_customer_loss_date)) 
VAR new_customers_at_period_start_rt =  CALCULATE(COUNTROWS('xero contacts'), ALL(), FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= end_of_previous_period && 'xero contacts'[Became Customer] >= beginning_of_time))VAR customers_lost_at_period_start_rt = CALCULATE(COUNTROWS('xero contacts'), ALL(), FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] < period_start_customer_loss_date && 'xero contacts'[Last Sales Activity] >= beginning_of_time))   
VAR net_customers_at_period_start = new_customers_at_period_start_rt - customers_lost_at_period_start_rt   
VAR churn = customers_lost_in_period / net_customers_at_period_start    
VAR months_in_period = DATEDIFF(earliest_date_to_consider, latest_date_to_consider + 1,MONTH)    
VAR annual_churn = 12/months_in_period * churn

RETURN annual_churn

This measure calculates annualised customer churn by identifying customers “lost” in the selected period (based on Last Sales Activity) and dividing by the net customers at the start of the period.
A customer is treated as lost only after a configurable inactivity window (Customer lost after months in Report Settings), and the result is scaled to an annual rate based on the number of months in the period.

Xero contacts

Customer Lifetime 

Customer Lifetime = VAR lifetime_in_months = 1/[Customer Churn] * 12
RETURN lifetime_in_months

This measure estimates customer lifetime in months by inverting the annualised Customer Churn rate.
It assumes a stable churn rate and calculates how long, on average, a customer is expected to remain active.

Xero contacts

Customers

Customers = [Customers Won RT] - [Customers Lost RT]

This measure calculates the net number of customers by subtracting Customers Lost (running total) from Customers Won (running total).
It represents the current active customer count within the selected filter context.

Xero contacts

Customers Active 

Customers Active = 

VAR earliest_date = EOMONTH(min('xero dates'[Date]),-1)+1
VAR latest_date = EOMONTH(max('xero dates'[Date]),0)
VAR days_in_period = DATEDIFF(
earliest_date, 
latest_date, 
DAY
)
VAR revenue_in_period = calculate(SUMX(
FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero invoices'[Invoice Date] >= earliest_date  && 'xero invoices'[Invoice Date] <= latest_date ),
[Net Amount (RC)]))

VAR new_customers_in_period = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= latest_date && 'xero contacts'[Became Customer] >= earliest_date), 'xero contacts'[Contact Name])

VAR customers_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = "Revenue" 
            && 'xero invoices'[Invoice Date] >= earliest_date  
            && 'xero invoices'[Invoice Date] <= latest_date 
            && 'xero invoices'[Status] <> "FORECAST")
        )

RETURN customers_in_period

This measure returns the number of active customers in the selected period by counting the distinct invoice contact names that have Revenue invoices dated within the period.
It excludes invoices with Status = “FORECAST”, so only customers with actual (non-forecast) revenue activity are counted.

Xero contacts

Customers Lost

Customers Lost = 
    VAR beginning_of_time = Date(1900,1,1)
	VAR first_date_in_period = EOMONTH(min('xero dates'[Date]),-1)+1
	VAR last_date_in_period = EOMONTH(max('xero dates'[Date]),0)
    VAR start_of_this_month = EOMONTH(TODAY(),-1)+1
    VAR end_of_this_month = EOMONTH(TODAY(),0)

    VAR consider_customer_lost_after_months = CALCULATE(MIN('Report Settings'[Value]), FILTER(ALL('Report Settings'), 'Report Settings'[Setting] = ""Customer lost after months""))

    VAR todays_latest_customer_loss_date = EOMONTH(end_of_this_month,-consider_customer_lost_after_months-1)+1

	VAR latest_customer_loss_date = IF(last_date_in_period > todays_latest_customer_loss_date, todays_latest_customer_loss_date, EOMONTH(last_date_in_period,-consider_customer_lost_after_months))
    VAR earliest_customer_loss_date = IF(first_date_in_period > todays_latest_customer_loss_date, todays_latest_customer_loss_date, EOMONTH(first_date_in_period,-(consider_customer_lost_after_months + 1))+1)
    
    VAR latest_date_to_consider = IF(last_date_in_period >= end_of_this_month, end_of_this_month, last_date_in_period)

    VAR earliest_date_to_consider = IF(last_date_in_period >= end_of_this_month, end_of_this_month, earliest_customer_loss_date)
    
	VAR months_in_period = DATEDIFF(earliest_date_to_consider, latest_date_to_consider,MONTH) 
	
	VAR new_customers_in_period = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= last_date_in_period && 'xero contacts'[Became Customer] >= first_date_in_period), 'xero contacts'[Contact Name])

	VAR new_customers_in_period_rt = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= last_date_in_period && 'xero contacts'[Became Customer] >= beginning_of_time), 'xero contacts'[Contact Name])
	
	VAR customers_active_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
		FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = ""Revenue"" 
            && 'xero invoices'[Invoice Date] >= earliest_date_to_consider  
            && 'xero invoices'[Invoice Date] <= latest_date_to_consider 
            && 'xero invoices'[Status] <> ""FORECAST"")
        )
		
	VAR customers_lost_in_period =  CALCULATE(COUNTROWS('xero contacts'), ALL(), FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] < latest_customer_loss_date && 'xero contacts'[Last Sales Activity] >= earliest_customer_loss_date))

	VAR customers_lost_in_period_rt = CALCULATE(COUNTROWS('xero contacts'), ALL(), FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] < latest_customer_loss_date && 'xero contacts'[Last Sales Activity] >= beginning_of_time))

RETURN customers_lost_in_period

This measure counts customers lost in the selected period by checking contacts whose Last Sales Activity falls within a calculated “loss window”.
The loss window is controlled by Customer lost after months (Report Settings), meaning customers are only treated as lost after being inactive for that many months, and the calculation is capped to avoid counting future periods.

Xero contacts

Customers Lost RT

Customers Lost RT = 
    VAR beginning_of_time = Date(1900,1,1)
	VAR first_date_in_period = EOMONTH(min('xero dates'[Date]),-1)+1
	VAR last_date_in_period = EOMONTH(max('xero dates'[Date]),0)
    VAR start_of_this_month = EOMONTH(TODAY(),-1)+1
    VAR end_of_this_month = EOMONTH(TODAY(),0)

    VAR consider_customer_lost_after_months = CALCULATE(MIN('Report Settings'[Value]), FILTER(ALL('Report Settings'), 'Report Settings'[Setting] = ""Customer lost after months""))

    VAR todays_latest_customer_loss_date = EOMONTH(end_of_this_month,-consider_customer_lost_after_months-1)+1

	VAR latest_customer_loss_date = IF(last_date_in_period > todays_latest_customer_loss_date, todays_latest_customer_loss_date, EOMONTH(last_date_in_period,-consider_customer_lost_after_months))
    VAR earliest_customer_loss_date = IF(first_date_in_period > todays_latest_customer_loss_date, todays_latest_customer_loss_date, EOMONTH(first_date_in_period,-(consider_customer_lost_after_months + 1))+1)
    
    VAR latest_date_to_consider = IF(last_date_in_period >= end_of_this_month, end_of_this_month, last_date_in_period)
    VAR earliest_date_to_consider = IF(last_date_in_period >= end_of_this_month, end_of_this_month, earliest_customer_loss_date)
    
	VAR months_in_period = DATEDIFF(earliest_date_to_consider, latest_date_to_consider,MONTH) 
	
	VAR new_customers_in_period = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= last_date_in_period && 'xero contacts'[Became Customer] >= first_date_in_period), 'xero contacts'[Contact Name])

	VAR new_customers_in_period_rt = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= last_date_in_period && 'xero contacts'[Became Customer] >= beginning_of_time), 'xero contacts'[Contact Name])
	
	VAR customers_active_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
		FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = ""Revenue"" 
            && 'xero invoices'[Invoice Date] >= earliest_date_to_consider  
            && 'xero invoices'[Invoice Date] <= latest_date_to_consider 
            && 'xero invoices'[Status] <> ""FORECAST"")
        )
		
	VAR customers_lost_in_period =  CALCULATE(COUNTROWS('xero contacts'), ALL(), FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] < latest_customer_loss_date && 'xero contacts'[Last Sales Activity] >= earliest_customer_loss_date))

	VAR customers_lost_in_period_rt = CALCULATE(COUNTROWS('xero contacts'), ALL(), FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] < latest_customer_loss_date && 'xero contacts'[Last Sales Activity] >= beginning_of_time))

RETURN customers_lost_in_period_rt

This measure calculates the running total of customers lost up to the selected period by counting contacts whose Last Sales Activity indicates they are lost, based on the inactivity threshold (Customer lost after months) in Report Settings.
It accumulates lost customers from the beginning of time to the current period, and caps the calculation to avoid counting future periods.

Xero contacts

Customers Won

Customers Won = 

VAR earliest_date = EOMONTH(min('xero dates'[Date]),-1)+1
VAR latest_date = EOMONTH(max('xero dates'[Date]),0)
VAR days_in_period = DATEDIFF(
earliest_date, 
latest_date, 
DAY
)
VAR new_customers_in_period =  CALCULATE(COUNTROWS('xero contacts'), ALL(), FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= latest_date && 'xero contacts'[Became Customer] >= earliest_date))

RETURN new_customers_in_period

This measure counts the number of customers won in the selected period based on the Became Customer date.
It includes all contacts whose customer start date falls between the start and end of the current reporting period.

Xero contacts

Customers Won RT

Customers Won RT = 

VAR earliest_date = date(1900,01,01)
VAR latest_date = EOMONTH(max('xero dates'[Date]),0)
VAR days_in_period = DATEDIFF(
earliest_date, 
latest_date, 
DAY
)

VAR new_customers_in_period =  CALCULATE(COUNTROWS('xero contacts'), ALL(), FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= latest_date && 'xero contacts'[Became Customer] >= earliest_date))

RETURN new_customers_in_period

This measure calculates the running total of customers won by counting all contacts whose Became Customer date is between 01/01/1900 and the end of the currently selected month.
It ignores other filters on the contacts table, so it returns the cumulative number of customers acquired up to that point in time.

Xero dates

Balance Sheet Report Title

Balance Sheet Report Title = "| Balance Sheet " & FIRSTNONBLANK('xero dates'[Fiscal Year Long Name],'xero dates'[Fiscal Year Long Name])

This measure generates a dynamic Balance Sheet title by appending the current fiscal year name to the text “| Balance Sheet”.
It uses the first available fiscal year value in the current filter context, allowing the report title to update automatically based on the selected period.

Xero dates

Profit and Loss Report Title

Profit and Loss Report Title = "| Profit and Loss " & FIRSTNONBLANK('xero dates'[Fiscal Year Long Name],'xero dates'[Fiscal Year Long Name])

This measure generates a dynamic Profit and Loss report title by appending the current fiscal year name to the text “| Profit and Loss”.
It automatically updates based on the fiscal year in the current filter context.

Xero invoices

% Sales by Country

% Sales by Country = 
VAR __BASELINE_VALUE = calculate(SUM('xero invoices'[Net Amount (RC)]), all('xero contacts'[Billing Address Country]))
VAR __VALUE_TO_COMPARE = SUM('xero invoices'[Net Amount (RC)])
RETURN
IF(
NOT ISBLANK(__VALUE_TO_COMPARE),
DIVIDE(__VALUE_TO_COMPARE, __BASELINE_VALUE)
)

This measure calculates the percentage of total sales by country in reporting currency (RC).
It divides the sales value for the current country by total sales across all countries, allowing you to see each country’s share of overall revenue.

Xero invoices

ARPU

ARPU = 
    VAR average_spend = 'xero invoices'[Average Spend] 
    VAR twelve_months = 12
    VAR consider_customers_lost_after_months = 1

    VAR beginning_of_time = Date(1900,1,1)
    VAR start_of_this_month = EOMONTH(TODAY(),-1)+1
    VAR end_of_this_month = EOMONTH(TODAY(),0)

	VAR last_date_in_period = EOMONTH(max('xero dates'[Date]),0)
	VAR first_date_in_period = EOMONTH(min('xero dates'[Date]),-1)+1
    
	VAR latest_date_a_year_ago = IF(last_date_in_period >= end_of_this_month, beginning_of_time, EOMONTH(max('xero dates'[Date]),-twelve_months))
    VAR earliest_date_a_year_ago = IF(last_date_in_period > end_of_this_month, beginning_of_time, EOMONTH(min('xero dates'[Date]),-(twelve_months))+1)
    
	VAR latest_date_two_years_ago = IF(last_date_in_period >= end_of_this_month, beginning_of_time, EOMONTH(max('xero dates'[Date]),-2 * twelve_months))
    
    VAR latest_period_date_considered = IF(last_date_in_period >= end_of_this_month, end_of_this_month, last_date_in_period)
    VAR earliest_period_date_considered = IF(last_date_in_period >= end_of_this_month, end_of_this_month, earliest_date_a_year_ago)
    
	VAR months_in_period = DATEDIFF(first_date_in_period, last_date_in_period + 1,MONTH) 

	VAR revenue_in_period = calculate(SUMX(
		FILTER(('xero invoices'), RELATED('xero accounts'[Class]) = ""Revenue"" 
            && 'xero invoices'[Invoice Date] >= earliest_period_date_considered  
            && 'xero invoices'[Invoice Date] <= latest_period_date_considered 
            && 'xero invoices'[Status] <> ""FORECAST""),
		[Net Amount (RC)]))
	
	VAR monthly_mrr_in_period = calculate(SUMX(
		FILTER(('xero invoices'), RELATED('xero accounts'[Class]) = ""Revenue"" 
            && 'xero invoices'[Invoice Date] >= first_date_in_period  
            && 'xero invoices'[Invoice Date] <= last_date_in_period 
            && 'xero invoices'[Status] <> ""FORECAST""),
		[Net Amount (RC)])) 
	
	VAR monthly_mrr_customers_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
		FILTER(('xero invoices'), RELATED('xero accounts'[Class]) = ""Revenue"" 
            && 'xero invoices'[Invoice Date] >= first_date_in_period  
            && 'xero invoices'[Invoice Date] <= last_date_in_period 
            && 'xero invoices'[Status] <> ""FORECAST"")
        )
		
    VAR customers_active_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
		FILTER(('xero invoices'), RELATED('xero accounts'[Class]) = ""Revenue"" 
            && 'xero invoices'[Invoice Date] >= first_date_in_period  
            && 'xero invoices'[Invoice Date] <= last_date_in_period 
            && 'xero invoices'[Status] <> ""FORECAST"")
        )

	VAR new_customers_in_period = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= last_date_in_period && 'xero contacts'[Became Customer] >= first_date_in_period), 'xero contacts'[Contact Name])
	VAR new_customers_in_period_rt = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= last_date_in_period && 'xero contacts'[Became Customer] >= beginning_of_time), 'xero contacts'[Contact Name])
	VAR new_customers_in_previous_period_rt = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= latest_date_a_year_ago && 'xero contacts'[Became Customer] >= beginning_of_time), 'xero contacts'[Contact Name])
	
	VAR customers_lost_in_period = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] <= latest_date_a_year_ago && 'xero contacts'[Last Sales Activity] >= earliest_date_a_year_ago), 'xero contacts'[Contact Name])
	VAR customers_lost_in_period_rt = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] <= latest_date_a_year_ago && 'xero contacts'[Last Sales Activity] >= beginning_of_time), 'xero contacts'[Contact Name])
	VAR customers_lost_in_previous_period_rt = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] <= latest_date_two_years_ago && 'xero contacts'[Last Sales Activity] >= beginning_of_time), 'xero contacts'[Contact Name])

    VAR net_customers = new_customers_in_period_rt + customers_lost_in_period_rt

    VAR ARPU = (monthly_mrr_in_period) / (monthly_mrr_customers_in_period)

    VAR average_spend_annualised = revenue_in_period / customers_active_in_period


RETURN ARPU

This measure calculates ARPU (Average Revenue Per User) by taking the total monthly revenue (RC) in the selected period and dividing it by the number of distinct customers with revenue invoices in that same period.
It excludes FORECAST invoices, so ARPU reflects only actual billed revenue for the current filter context.

Xero invoices

Average Spend 

Average Spend = 

VAR earliest_date = EOMONTH(min('xero dates'[Date]),-1)+1
VAR latest_date = EOMONTH(max('xero dates'[Date]),0)
VAR days_in_period = DATEDIFF(
earliest_date, 
latest_date, 
DAY
)
VAR revenue_in_period = calculate(SUMX(
FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero invoices'[Invoice Date] >= earliest_date  && 'xero invoices'[Invoice Date] <= latest_date ),
[Net Amount (RC)]))

VAR new_customers_in_period = CALCULATE(DISTINCTCOUNT('xero contacts'[Contact ID]),
FILTER(all('xero contacts'), 'xero contacts'[Became Customer] >= earliest_date  && 'xero contacts'[Became Customer] <= latest_date )
)

VAR customers_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero invoices'[Invoice Date] >= earliest_date  && 'xero invoices'[Invoice Date] <= latest_date )
        )

RETURN revenue_in_period / customers_in_period

This measure calculates average spend per customer by dividing total revenue (RC) in the selected period by the number of distinct customers with revenue invoices in that period.
It reflects the average revenue generated per active customer for the current date context.

Xero invoices

Customer LTV 

Customer LTV = 'xero invoices'[ARPU] * 'xero contacts'[Customer Lifetime] / 12

This measure calculates Customer Lifetime Value (LTV) by multiplying ARPU by Customer Lifetime, then normalising it to a monthly basis.
It estimates the total revenue a customer generates over their lifetime in the current filter context.

Xero invoices

Customer MRR 

Customer MRR = 
    VAR average_spend = 'xero invoices'[Average Spend] 
    VAR twelve_months = 12
    VAR beginning_of_time = Date(1900,1,1)
    VAR start_of_this_month = EOMONTH(TODAY(),-1)+1
    VAR end_of_this_month = EOMONTH(TODAY(),0)
	VAR last_date_in_period = EOMONTH(max('xero dates'[Date]),0)
	VAR first_date_in_period = EOMONTH(min('xero dates'[Date]),-1)+1
	VAR latest_date_a_year_ago = IF(last_date_in_period >= end_of_this_month, beginning_of_time, EOMONTH(max('xero dates'[Date]),-twelve_months))
    VAR earliest_date_a_year_ago = IF(last_date_in_period > end_of_this_month, beginning_of_time, EOMONTH(min('xero dates'[Date]),-(twelve_months))+1)
	VAR latest_date_two_years_ago = IF(last_date_in_period >= end_of_this_month, beginning_of_time, EOMONTH(max('xero dates'[Date]),-2 * twelve_months))
    VAR latest_period_date_considered = IF(last_date_in_period >= end_of_this_month, end_of_this_month, last_date_in_period)
    VAR earliest_period_date_considered = IF(last_date_in_period >= end_of_this_month, end_of_this_month, earliest_date_a_year_ago)
	VAR months_in_period = DATEDIFF(first_date_in_period, last_date_in_period + 1,MONTH) 
	VAR revenue_in_period = calculate(SUMX(
		FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = ""Revenue"" 
            && 'xero invoices'[Contact ID] = RELATED('xero contacts'[Contact ID])
            && 'xero invoices'[Invoice Date] >= earliest_period_date_considered  
            && 'xero invoices'[Invoice Date] <= latest_period_date_considered 
            && 'xero invoices'[Status] <> ""FORECAST""),
		[Net Amount (RC)]))
    VAR monthly_mrr_in_period = calculate(SUMX( RELATEDTABLE('xero invoices'), [Net Amount (RC)]), 
        filter('xero invoices', 
            'xero invoices'[Invoice Date] >= first_date_in_period  
            && 'xero invoices'[Invoice Date] <= last_date_in_period
            && 'xero invoices'[Status] <> ""FORECAST"")
      )
	VAR monthly_mrr_customers_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Customer Month]),
		FILTER(('xero invoices'), RELATED('xero accounts'[Class]) = ""Revenue"" 
            && 'xero invoices'[Invoice Date] >= first_date_in_period  
            && 'xero invoices'[Invoice Date] <= last_date_in_period 
            && 'xero invoices'[Status] <> ""FORECAST"")
        )
		
    VAR customers_active_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
		FILTER(('xero invoices'), RELATED('xero accounts'[Class]) = ""Revenue"" 
            && 'xero invoices'[Invoice Date] >= first_date_in_period  
            && 'xero invoices'[Invoice Date] <= last_date_in_period 
            && 'xero invoices'[Status] <> ""FORECAST"")
        )
	VAR new_customers_in_period = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= last_date_in_period && 'xero contacts'[Became Customer] >= first_date_in_period), 'xero contacts'[Contact Name])
	VAR new_customers_in_period_rt = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= last_date_in_period && 'xero contacts'[Became Customer] >= beginning_of_time), 'xero contacts'[Contact Name])
	VAR new_customers_in_previous_period_rt = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Became Customer] <= latest_date_a_year_ago && 'xero contacts'[Became Customer] >= beginning_of_time), 'xero contacts'[Contact Name])
	VAR customers_lost_in_period = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] <= latest_date_a_year_ago && 'xero contacts'[Last Sales Activity] >= earliest_date_a_year_ago), 'xero contacts'[Contact Name])
	VAR customers_lost_in_period_rt = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] <= latest_date_a_year_ago && 'xero contacts'[Last Sales Activity] >= beginning_of_time), 'xero contacts'[Contact Name])
	VAR customers_lost_in_previous_period_rt = COUNTX(FILTER(ALL('xero contacts'), 'xero contacts'[Last Sales Activity] <= latest_date_two_years_ago && 'xero contacts'[Last Sales Activity] >= beginning_of_time), 'xero contacts'[Contact Name])
    VAR net_customers = new_customers_in_period_rt + customers_lost_in_period_rt
    VAR ARPU = (monthly_mrr_in_period) / (monthly_mrr_customers_in_period) 
    VAR average_spend_annualised = revenue_in_period / customers_active_in_period

RETURN (monthly_mrr_in_period / if (monthly_mrr_customers_in_period = 0 , 1, monthly_mrr_customers_in_period))

This measure calculates Customer MRR (Monthly Recurring Revenue per customer) by dividing total monthly revenue (RC) in the selected period by the count of unique customer-month combinations.
It excludes FORECAST invoices and uses a safe divide (defaults the divisor to 1 when zero) to avoid errors in periods with no customers.

Xero invoices

DSO

DSO = 
VAR earliest_date = min('xero dates'[Date])
VAR latest_date = max('xero dates'[Date])
VAR days_in_period = DATEDIFF(
earliest_date, 
latest_date, 
DAY
)

VAR outstanding_at_earliest_date = CALCULATE(
SUM('xero invoices'[Gross Amount (RC)]),
FILTER(
all('xero invoices'), 
'xero invoices'[Invoice Date] < earliest_date
&& ('xero invoices'[Fully Paid On Date] > earliest_date || ISBLANK('xero invoices'[Fully Paid On Date]))
&& ('xero invoices'[Status] = "AUTHORISED" || 'xero invoices'[Status] = "PAID" )
),
FILTER ('xero accounts', 'xero accounts'[Class] = "Revenue")
)

VAR outstanding_at_latest_date = CALCULATE(
SUM('xero invoices'[Gross Amount (RC)]),
FILTER(
all('xero invoices'), 
'xero invoices'[Invoice Date] < latest_date
&& ('xero invoices'[Fully Paid On Date] > latest_date || ISBLANK('xero invoices'[Fully Paid On Date]))
&& ('xero invoices'[Status] = "AUTHORISED" || 'xero invoices'[Status] = "PAID" )
),
FILTER ('xero accounts', 'xero accounts'[Class] = "Revenue")
)
VAR invoicing_in_period = CALCULATE(
SUM('xero invoices'[Gross Amount (RC)]),
FILTER(
all('xero invoices'), 
'xero invoices'[Invoice Date] >= earliest_date
&& 'xero invoices'[Invoice Date] <= latest_date
&& ('xero invoices'[Status] = "AUTHORISED" || 'xero invoices'[Status] = "PAID" ) 
),
FILTER ('xero accounts', 'xero accounts'[Class] = "Revenue")
)

VAR calculated_dso = ((outstanding_at_earliest_date + outstanding_at_latest_date) / 2) / invoicing_in_period *  days_in_period

RETURN
calculated_dso

This measure calculates DSO (Days Sales Outstanding) for the selected period by estimating average outstanding receivables and dividing by invoicing activity.
It takes the average of outstanding invoice balances at the start and end of the period (for authorised/paid revenue invoices not yet fully paid), then scales by days in period to return the average collection time in days.

Xero invoices

Export Revenue (RC) 

Export Revenue (RC) = CALCULATE(sum('xero invoices'[Net Amount (RC)]),filter('xero contacts', 'xero contacts'[Is Export] = True))

This measure calculates export revenue in reporting currency (RC) by summing invoice net amounts for customers marked as Export.
It filters invoices based on the Is Export flag on contacts, returning total export sales for the current report context.

Xero invoices

Export Revenue % 

Export Revenue % = CALCULATE(sum('xero invoices'[Net Amount (RC)]),filter('xero contacts', 'xero contacts'[Is Export] = True)) / (sum('xero invoices'[Net Amount (RC)]))

This measure calculates the percentage of total revenue that comes from export customers.
It divides export revenue (RC) by total revenue (RC), showing the share of sales generated from customers marked as Is Export = True.

Xero invoices

Months in Period 

Months in Period = 

VAR earliest_date = EOMONTH(min('xero dates'[Date]),-1)+1
VAR latest_date = EOMONTH(max('xero dates'[Date]),0)
    VAR end_of_this_month = EOMONTH(TODAY(),0)
    VAR latest_date_not_in_future_month = IF(latest_date > end_of_this_month, end_of_this_month, latest_date)
    VAR earliest_date_not_in_future_month = IF(earliest_date > end_of_this_month, end_of_this_month, earliest_date)

VAR months_in_period = DATEDIFF(earliest_date_not_in_future_month, latest_date_not_in_future_month,MONTH) + 1

RETURN months_in_period

This measure calculates the number of months in the selected period, capped so it does not extend into future months.
It counts full months between the period start and end (based on the date context), ensuring consistent results when future dates are included in the report.

Xero invoices

This Period Existing Business 

This Period Existing Business = 

VAR earliest_date = EOMONTH(min('xero dates'[Date]),-1)+1
VAR latest_date = EOMONTH(max('xero dates'[Date]),0)
VAR days_in_period = DATEDIFF(
earliest_date, 
latest_date, 
DAY
)
VAR revenue_in_period = calculate(SUMX(
FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero invoices'[Invoice Date] >= earliest_date  && 'xero invoices'[Invoice Date] <= latest_date ),
[Net Amount (RC)])
        )

VAR new_customers_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
FILTER(all('xero contacts'), 'xero contacts'[Became Customer] >= earliest_date  && 'xero contacts'[Became Customer] <= latest_date )
)

VAR new_customer_revenue_in_period = calculate(SUM('xero invoices'[Net Amount (RC)]), FILTER('xero contacts','xero contacts'[Became Customer] >= earliest_date  && 'xero contacts'[Became Customer] <= latest_date ))
    
VAR customers_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero invoices'[Invoice Date] >= earliest_date  && 'xero invoices'[Invoice Date] <= latest_date )
        )

    VAR existing_business_revenue_in_period = revenue_in_period - new_customer_revenue_in_period

RETURN existing_business_revenue_in_period

This measure calculates existing business revenue (RC) for the selected period by subtracting new customer revenue from total revenue.
“New customers” are those whose Became Customer date falls within the period, so the result represents revenue generated from customers who were already customers before the period started.

Xero invoices

This Period MRR

This Period MRR = calculate(SUMX(
FILTER('xero invoices', RELATED('xero accounts'[Class]) = "Revenue"),
[Net Amount (RC)]),
filter(all('xero dates'), 'xero dates'[Date] <=EOMONTH(max('xero dates'[Date]),0) && 'xero dates'[Date] > EOMONTH(max('xero dates'[Date]),-1 ))
)

This measure calculates MRR (RC) for the current month by summing revenue invoice amounts within the latest month in the current date context.
It overrides the date filter to include only dates from the start of that month to the month end, returning monthly recurring revenue for that period.

Xero invoices

This Period New Business 

This Period New Business = 

VAR earliest_date = EOMONTH(min('xero dates'[Date]),-1)+1
VAR latest_date = EOMONTH(max('xero dates'[Date]),0)
VAR days_in_period = DATEDIFF(
earliest_date, 
latest_date, 
DAY
)
VAR revenue_in_period = calculate(SUMX(
FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero invoices'[Invoice Date] >= earliest_date  && 'xero invoices'[Invoice Date] <= latest_date ),
[Net Amount (RC)])
        )

VAR new_customers_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
FILTER(all('xero contacts'), 'xero contacts'[Became Customer] >= earliest_date  && 'xero contacts'[Became Customer] <= latest_date )
)

VAR new_customer_revenue_in_period = calculate(SUM('xero invoices'[Net Amount (RC)]), FILTER('xero contacts','xero contacts'[Became Customer] >= earliest_date  && 'xero contacts'[Became Customer] <= latest_date ))
    
VAR customers_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero invoices'[Invoice Date] >= earliest_date  && 'xero invoices'[Invoice Date] <= latest_date )
        )

RETURN new_customer_revenue_in_period

This measure calculates new business revenue (RC) for the selected period by summing invoice net amounts for customers whose Became Customer date falls within the period.
It returns the revenue generated from newly acquired customers, based on the current date context.

Xero invoices

This Period New Business %

This Period New Business % = 

VAR earliest_date = EOMONTH(min('xero dates'[Date]),-1)+1
VAR latest_date = EOMONTH(max('xero dates'[Date]),0)
VAR days_in_period = DATEDIFF(
earliest_date, 
latest_date, 
DAY
)
VAR revenue_in_period = calculate(SUMX(
FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero invoices'[Invoice Date] >= earliest_date  && 'xero invoices'[Invoice Date] <= latest_date && ( 'xero invoices'[Status] = "AUTHORISED" || 'xero invoices'[Status] = "PAID")),
[Net Amount (RC)])
        )

VAR new_customers_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
FILTER(all('xero contacts'), 'xero contacts'[Became Customer] >= earliest_date  && 'xero contacts'[Became Customer] <= latest_date )
)

VAR new_customer_revenue_in_period = calculate(SUM('xero invoices'[Net Amount (RC)]), FILTER('xero contacts','xero contacts'[Became Customer] >= earliest_date  && 'xero contacts'[Became Customer] <= latest_date ))
    
VAR customers_in_period = CALCULATE(DISTINCTCOUNT('xero invoices'[Contact Name]),
FILTER(all('xero invoices'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero invoices'[Invoice Date] >= earliest_date  && 'xero invoices'[Invoice Date] <= latest_date )
        )

    VAR existing_business_revenue_in_period = revenue_in_period - new_customer_revenue_in_period

RETURN new_customer_revenue_in_period / revenue_in_period

This measure calculates the percentage of revenue from new customers in the selected period by dividing new customer revenue (RC) by total revenue (RC).
It treats customers as “new” when their Became Customer date falls within the period, and uses only authorised/paid revenue invoices for the total.

Xero journals

Direct Costs 

Direct Costs = calculate(SUM('xero journals'[Net Amount (RC)]), FILTER ('xero accounts', 'xero accounts'[Type] = "Directcosts"))

This measure calculates direct costs in reporting currency (RC) by summing journal net amounts for accounts classified as Directcosts.
It returns the total cost directly attributable to revenue generation for the current report context.

Xero journals

Expenses

Expenses = calculate(SUM('xero journals'[Net Amount (RC)]), FILTER ('xero accounts', 'xero accounts'[Class] = "Expense"))

This measure calculates total expenses in reporting currency (RC) by summing journal net amounts for accounts classified as Expense.
It returns all operating expenses for the current report context.

Xero journals

FY Revenue

FY Revenue = 
    VAR this_fy_start = calculate(min('xero dates'[Fiscal Year Start]), FILTER(ALL('xero dates'), 'xero dates'[Date] = TODAY()))
    VAR this_fy_end = calculate(min('xero dates'[Fiscal Year End]), FILTER(ALL('xero dates'), 'xero dates'[Date] = TODAY()))
VAR revenue_in_period = calculate(SUMX(
FILTER(('xero journals'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero journals'[Journal Date] >= this_fy_start  && 'xero journals'[Journal Date] <= this_fy_end),
[Net Amount (RC)])
        )

RETURN revenue_in_period

This measure calculates total revenue (RC) for the current fiscal year, using the fiscal year start and end dates that contain TODAY().
It filters journals to Revenue accounts and sums net amounts where the journal date falls within the current fiscal year.

Xero journals

FY YTD Revenue 

FY YTD Revenue = 
    VAR this_fy_start = calculate(min('xero dates'[Fiscal Year Start]), FILTER(ALL('xero dates'), 'xero dates'[Date] = TODAY()))
VAR revenue_in_period = calculate(SUMX(
FILTER(all('xero journals'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero journals'[Journal Date] >= this_fy_start  && 'xero journals'[Journal Date] <= TODAY()),
[Net Amount (RC)])
        )

RETURN revenue_in_period

This measure calculates fiscal year-to-date (YTD) revenue in reporting currency (RC) from the start of the current fiscal year up to TODAY().
It filters journals to Revenue accounts and sums net amounts where the journal date falls between the fiscal year start and today.

Xero journals

Gross Profit

Gross Profit = [Revenue] + [Direct Costs]

This measure calculates Gross Profit by combining Revenue with Direct Costs.
It represents profit after direct costs are applied, before operating expenses are taken into account.

Xero journals

Gross Profit %

Gross Profit % = [Gross Profit]/[Revenue]

This measure calculates Gross Profit % by dividing Gross Profit by Revenue.
It shows the proportion of revenue retained after direct costs, expressed as a percentage.

Xero journals

Journals Net Amount (RC) ABS 

Journals Net Amount (RC) ABS = (CALCULATE(sum('xero journals'[Net Amount (RC)]),KEEPFILTERS('xero accounts'[Class] = "Expense")) * -1) + (CALCULATE(sum('xero journals'[Net Amount (RC)]),KEEPFILTERS('xero accounts'[Class] = "Revenue")))

This measure returns a combined absolute value for journals, where Expense amounts are flipped to positive values and Revenue amounts remain positive.
It applies account class filters using KEEPFILTERS and merges both results into a single absolute net amount in reporting currency (RC) for the current context.

Xero journals

Journals Net Amount (RC) RT 

Journals Net Amount (RC) RT = CALCULATE(sum('xero journals'[Net Amount (RC)]),filter(all('xero dates'), 'xero dates'[Date] <= max('xero dates'[Date])))

This measure calculates a running total of journal net amounts in reporting currency (RC).
It removes existing date filters and sums all journal amounts up to the current date in the visual, showing a cumulative value over time.

Xero journals

Marketing Spend (RC) 

Marketing Spend (RC) = 

VAR earliest_date = EOMONTH(min('xero dates'[Date]),-1)+1
VAR latest_date = EOMONTH(max('xero dates'[Date]),0)

VAR marketing_spend = CALCULATE(sum('xero journals'[Net Amount (RC)]),
        filter(all('xero journals'), CONTAINSSTRING(RELATED('xero accounts'[Account Name]), "Marketing") && 'xero journals'[Journal Type] <> "FORECAST INVOICE"), 
        filter(all('xero dates'),'xero dates'[Date] >= earliest_date && 'xero dates'[Date] <= latest_date)
    )

RETURN -1 * marketing_spend

This measure calculates marketing spend (RC) for the selected period by summing journal net amounts where the account name contains “Marketing”, excluding FORECAST INVOICE journals.
The result is multiplied by -1 so marketing costs are shown as a positive spend value.

Xero journals

Net Profit

Net Profit = [Revenue] + [Expenses]

This measure calculates Net Profit by combining Revenue with Expenses.
It represents the final profit or loss after all operating expenses are included.

Xero journals

Net Profit %

Net Profit % = [Net Profit]/[Revenue]

This measure calculates Net Profit % by dividing Net Profit by Revenue.
It shows the proportion of revenue retained after all expenses, expressed as a percentage.

Xero journals

Previous FY Revenue 

Previous FY Revenue = 

VAR previous_fy_start = EDATE(min('xero dates'[Fiscal Year Start]), -12)
VAR previous_fy_end = EDATE(min('xero dates'[Fiscal Year End]), -12)
VAR revenue_in_period = calculate(SUMX(
FILTER(all('xero journals'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero journals'[Journal Date] >= previous_fy_start  && 'xero journals'[Journal Date] <= previous_fy_end ),
[Net Amount (RC)])
        )

RETURN revenue_in_period

This measure calculates total revenue (RC) for the previous fiscal year by shifting the current fiscal year start and end dates back 12 months.
It then filters journals to Revenue accounts and sums net amounts where the journal date falls within that prior fiscal year period.

Xero journals

Previous FY TD Revenue

Previous FY TD Revenue = 
VAR previous_fy_start = EDATE(min('xero dates'[Fiscal Year Start]), -12)
VAR one_year_ago = EDATE(TODAY(), -12)
VAR revenue_in_period = calculate(SUMX(
FILTER(all('xero journals'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero journals'[Journal Date] >= previous_fy_start  && 'xero journals'[Journal Date] <= one_year_ago),
[Net Amount (RC)])
        )

RETURN revenue_in_period

This measure calculates previous fiscal year-to-date revenue (RC) by summing revenue journals from the start of the prior fiscal year up to the same date one year ago.
It filters to Revenue accounts, providing a like-for-like YTD comparison against the current year.

Xero journals

Revenue 

Revenue = calculate(SUM('xero journals'[Net Amount (RC)]), FILTER ('xero accounts', 'xero accounts'[Class] = "Revenue"))

This measure calculates total revenue in reporting currency (RC) by summing journal net amounts for accounts classified as Revenue.
It returns overall revenue for the current report context.

Xero journals

This Month Revenue

This Month Revenue = 

VAR month_start = calculate(min('xero dates'[Fiscal Month Start]), FILTER('xero dates', 'xero dates'[Date] = TODAY()))
VAR month_end = calculate(min('xero dates'[Fiscal Month End]), FILTER('xero dates', 'xero dates'[Date] = TODAY()))
VAR revenue_in_period = calculate(SUMX(
FILTER(all('xero journals'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero journals'[Journal Date] >= month_start  && 'xero journals'[Journal Date] <= month_end ),
[Net Amount (RC)])
        )

RETURN revenue_in_period

This measure calculates revenue (RC) for the current fiscal month, using the fiscal month start and end dates that contain TODAY().
It filters journals to Revenue accounts and sums net amounts where the journal date falls within that fiscal month.

Xero journals

This Month Revenue Last Year 

This Month Revenue Last Year = 

VAR previous_fy_month_start = EDATE(calculate(min('xero dates'[Fiscal Month Start]), FILTER('xero dates', 'xero dates'[Date] = TODAY())), -12)
VAR previous_fy_month_end = EDATE(calculate(min('xero dates'[Fiscal Month End]), FILTER('xero dates', 'xero dates'[Date] = TODAY())), -12)
VAR revenue_in_period = calculate(SUMX(
FILTER(all('xero journals'), RELATED('xero accounts'[Class]) = "Revenue" && 'xero journals'[Journal Date] >= previous_fy_month_start  && 'xero journals'[Journal Date] <= previous_fy_month_end ),
[Net Amount (RC)])
        )

RETURN revenue_in_period

This measure calculates revenue (RC) for the same fiscal month last year by shifting the current fiscal month start and end dates back 12 months.
It then sums revenue journal amounts within that prior-month period for the current report context.

Xero payments

Bank Amount (RC) RT 

Bank Amount (RC) RT = CALCULATE(sum('xero payments'[Bank Amount (RC)]),filter(all('xero dates'), 'xero dates'[Date] <= max('xero payments'[Payment Date])))

This measure calculates a running total of bank payments in reporting currency (RC).
It sums all payment amounts up to the latest payment date in the current context, showing a cumulative bank balance over time.

Xero quotes

This Period New Business Quotes %

This Period New Business Quotes % = 
    // Return the % by value of all new business quotes in the period
    
VAR earliest_date = EOMONTH(min('xero dates'[Date]),-1)+1
VAR latest_date = EOMONTH(max('xero dates'[Date]),0)
VAR days_in_period = DATEDIFF(
earliest_date, 
latest_date, 
DAY
)
VAR quotes_in_period = calculate(SUM('xero quotes'[Net Amount]),
FILTER(ALL('xero quotes'), 'xero quotes'[Quote Date] >= earliest_date  && 'xero quotes'[Quote Date] <= latest_date )
)
        

VAR new_business_quotes_in_period = calculate(SUMX(
FILTER('xero quotes', 'xero quotes'[Quote Date] >= earliest_date 
        && 'xero quotes'[Quote Date] <= latest_date
        && 'xero quotes'[Is New Business] = TRUE),
'xero quotes'[Net Amount]))
        

VAR existing_business_quotes_in_period = calculate(SUMX(
FILTER('xero quotes', 'xero quotes'[Quote Date] >= earliest_date 
        && 'xero quotes'[Quote Date] <= latest_date
        && 'xero quotes'[Is New Business] = FALSE),
'xero quotes'[Net Amount]))

RETURN  new_business_quotes_in_period / quotes_in_period

This measure calculates the percentage of quote value from new business in the selected period.
It divides the total Net Amount of quotes flagged Is New Business = TRUE by the total quote Net Amount for the same date range.

Xero quotes

Won %

Won % = 
VAR earliest_date = min('xero dates'[Date])
VAR latest_date = max('xero dates'[Date]) + 1

    VAR quoted_in_period = calculate(SUM('xero quotes'[Net Amount (RC)]),
            removefilters('xero quotes'),
'xero quotes'[Quote Date] >= earliest_date
&& 'xero quotes'[Quote Date] < latest_date
        )
      
    VAR won_in_period = calculate(SUMX( RELATEDTABLE('xero quotes'), [Net Amount (RC)]), 
        filter(('xero quotes'), 
'xero quotes'[Quote Date] >= earliest_date
&& 'xero quotes'[Quote Date] < latest_date
&& 'xero quotes'[Simple Quote status] = "Won"
        )
      )

VAR win_ratio = (won_in_period / quoted_in_period) 

return win_ratio

This measure calculates the win rate by value for the selected period by dividing the total Won quote value (RC) by the total quoted value (RC).
It uses quote dates within the current date context and counts only quotes with Simple Quote status = “Won” in the numerator.

Related Articles
Overview
Invoices
Journals
Budgets
Bank Transactions
Did this answer your question?