Reconcile a Power BI churn report to opening customer counts and recurring revenue. Prepare dated losses in a spreadsheet, compare segment results, and build ratios that use the correct denominator.
Churn analysis in Power BI divides customer losses by a fixed opening customer base and measures recurring-revenue losses against opening recurring revenue. Build the report from checked customer-period records, then reconcile the closing counts and amounts. That makes a filterable report useful for finance and renewal decisions.
Use July 1, 2026, through July 31, 2026, as the reporting month, with August 1 as the exclusive closing boundary. An opening customer counts as lost when service ends during July and the customer is inactive at that boundary. A canceled add-on held by a continuing customer is a contraction, rather than a lost customer.
Prepare one row per customer per month from your billing exports and customer registers. Include the period start, opening segment, opening and closing customer flags, effective service end date, opening and closing monthly recurring revenue, and a new customer flag.
Keep the original identifier and source dates for review. Aggregate multiple subscriptions before deciding whether the customer has left. This is a management reporting definition; it does not turn monthly recurring revenue, or MRR, into recognized revenue or cash receipts.
For the broader diagnostic process after the totals agree, use churn rate analysis.
In a Customers worksheet, use A for customer ID, B for opening segment, C for period start, D for the opening customer flag, E for effective cancellation date, F for the closing customer flag, G for opening MRR, H for closing MRR, and I for the new customer flag. Opening and closing flags come from their respective customer registers.
In J, calculate the lost customer flag. K holds canceled MRR, L contraction MRR, M expansion MRR, and N new MRR. Enter these formulas in row 2 and fill down:
J2 = --AND(D2=1,F2=0,E2<>"",E2>=C2,E2<EDATE(C2,1))
K2 = G2*J2
L2 = IF(AND(D2=1,F2=1),MAX(0,G2-H2),0)
M2 = IF(AND(D2=1,F2=1),MAX(0,H2-G2),0)
N2 = I2*H2
Store dates as real spreadsheet dates. These formulas assume no reactivations or temporary pauses, one currency, and no new customer who also leaves during July. Add separate movements for those cases when they occur; do not force them into acquisition or cancellation.
An opening customer missing from the closing register without a service end date needs an exception review. A formula cannot establish whether an absent row means a departure or an incomplete export.
A fictional US business software company has two opening segments. The customer-period records produce these July totals:
| Opening segment | Opening customers | Lost customers | New customers | Closing customers |
|---|---|---|---|---|
| Small business | 80 | 8 | 6 | 78 |
| Enterprise | 20 | 1 | 2 | 21 |
| Total | 100 | 9 | 8 | 99 |
The count bridge is opening customers minus lost customers plus new customers. Small business is 80 - 8 + 6 = 78; enterprise is 20 - 1 + 2 = 21. Together, 100 - 9 + 8 = 99, matching the closing customer register.
| Opening segment | Opening MRR (USD) | Canceled MRR (USD) | Contraction MRR (USD) | Expansion MRR (USD) | New MRR (USD) | Closing MRR (USD) |
|---|---|---|---|---|---|---|
| Small business | 16,000 | 1,200 | 800 | 400 | 1,500 | 15,900 |
| Enterprise | 24,000 | 3,000 | 1,200 | 2,400 | 3,600 | 25,800 |
| Total | 40,000 | 4,200 | 2,000 | 2,800 | 5,100 | 41,700 |
The revenue bridge is 40,000 - 4,200 - 2,000 + 2,800 + 5,100 = $41,700. Closing MRR grew even though the company lost nine opening customers. The revenue gains belong in the bridge, outside the churn numerator.
| Opening segment | Customer churn | Full-cancellation revenue churn | Gross revenue churn including contraction |
|---|---|---|---|
| Small business | 8 / 80 = 10.0% | 1,200 / 16,000 = 7.5% | 2,000 / 16,000 = 12.5% |
| Enterprise | 1 / 20 = 5.0% | 3,000 / 24,000 = 12.5% | 4,200 / 24,000 = 17.5% |
| Total | 9 / 100 = 9.0% | 4,200 / 40,000 = 10.5% | 6,200 / 40,000 = 15.5% |
The simple average of customer churn rates is (10.0% + 5.0%) / 2 = 7.5%, which understates the actual 9.0%. Percentages shown are rounded to one decimal place; calculate from the underlying counts and dollars.
Create a summary worksheet with period start in A, opening segment in B, opening customers in C, lost customers in D, opening MRR in E, and canceled MRR in F. For each segment-period row, sum matching customer records:
C2 = SUMIFS(Customers!$D:$D,Customers!$C:$C,$A2,Customers!$B:$B,$B2)
D2 = SUMIFS(Customers!$J:$J,Customers!$C:$C,$A2,Customers!$B:$B,$B2)
E2 = SUMIFS(Customers!$G:$G,Customers!$C:$C,$A2,Customers!$B:$B,$B2)
F2 = SUMIFS(Customers!$K:$K,Customers!$C:$C,$A2,Customers!$B:$B,$B2)
Add the remaining movement columns with the same matching criteria. For the two example segments in rows 2 and 3, the independent control rates are =SUM(D2:D3)/SUM(C2:C3) and =SUM(F2:F3)/SUM(E2:E3).
Keep totals out of the imported summary table. Importing both the segment rows and their total would count the same business twice. Retain the customer detail separately for investigating discrepancies.
In Power BI Desktop, choose Home > Get Data > Excel. Select the workbook, choose the checked summary table in Navigator, and select Load, following Microsoft's documented Excel workflow.
Build a table visual with period, opening segment, and the movement amounts. Use Sum for the customer-count and MRR columns. Display opening customers and opening MRR beside the churn rates so the denominators remain visible.
For Customer churn, choose New quick measure from a field's menu, select Division under Mathematical operations, and supply summed lost customers and summed opening customers. Repeat with canceled MRR and opening MRR for Full-cancellation revenue churn. Microsoft documents both the menu and Division option in its quick measures guide. Format the resulting measures as percentages.
Check the unfiltered totals against the worked example. Then select each opening segment and confirm both numerator and denominator change together. An empty opening base should remain undefined rather than being presented as successful retention. Avoid summing customer counts across months: those are repeated monthly populations, not distinct customers for the whole span.
For customer retention across acquisition periods, use cohort analysis in Power BI. The equivalent count-and-revenue reconciliation is in churn analysis in Tableau.
Covirage is not affiliated with Microsoft or Tableau.
Upload the prepared customer-period file or Google Sheet to Covirage with a sample of your own billing summaries. Covirage maps the columns, its tools compute and check the movements, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See Covirage for renewal monitoring for the business context.
Start with opening and closing customer registers, recurring billing balances, effective cancellation dates, and new customer records. Use a stable customer identifier across the files. A cancellation export alone cannot establish the customers who were at risk of leaving.
An unweighted average gives small segments as much influence as large ones. Calculate the total from summed lost customers divided by summed opening customers. Use summed lost recurring revenue divided by summed opening revenue for the corresponding revenue measure.
New customers increase the closing customer count but do not reduce losses from the opening population. Show acquisition separately in the customer bridge. Otherwise a strong sales month can make a weak retention month appear healthy.
Not necessarily. A full-cancellation measure includes recurring revenue lost when customers leave. Gross revenue churn can also include contractions among retained customers. Name the measure clearly and show both components so teams can reconcile the reported loss.