Cohort analysis in Power BI: retention by age

Cohort analysis in Power BI compares customers at the same age after entry. Start with a checked customer-age summary and display it as a matrix. Fix membership and separate completed observation windows from future ones.

Set the business definition outside the visual

A US office equipment supplier wants to know whether newer customers reorder. Entry is the first completed order; age zero is its calendar month. A retained customer places at least one completed order in the specified later calendar month. All dates use the same business timezone.

Keep one customer register row per account with a stable ID and original first-order date, including inactive customers. A recent order export cannot establish entry dates: an older customer's first visible row may be a repeat order.

Use a separate activity file with customer ID and completed order date. Define the treatment of canceled orders and internal accounts before calculating anything. For wider account segmentation, use customer analysis in Power BI; this workflow answers the narrower repeat-purchase question.

Retention at age n = Original customers with a qualifying order in completed age month n / Original cohort members

Prepare the periods and return flags in Excel

Create a row for every customer and age you intend to compare, including customers with no orders. Put customer ID in A, original first-order date in B, and age in C. Set L1 to May 31, 2026, with source data complete through that day's end.

In D2, calculate the age month start; in E2, the month end; in F2, whether the month is complete:

=EDATE(DATE(YEAR(B2),MONTH(B2),1),C2)
=EOMONTH(D2,0)
=--(E2<=$L$1)

Use customer IDs in Activity!A2:A1000 and order dates in Activity!B2:B1000. The return flag in G2 is:

=IF(F2=0,"",--(COUNTIFS(Activity!$A$2:$A$1000,A2,Activity!$B$2:$B$1000,">="&D2,Activity!$B$2:$B$1000,"<"&EDATE(D2,1))>0))

Microsoft's COUNTIFS guidance covers matching multiple conditions; testing for a positive count gives one return flag. Extend the ranges to include the complete activity file. For the workbook method, follow building a cohort retention table in Excel.

Work the supplier's example

The following is the complete customer membership and observed return table. Age one is April for March entrants and May for April entrants. Age two is May and June respectively.

Summarize one row per entry cohort and age before importing:

Percentages are rounded to one decimal place. March age one is 2 / 3, March age two is 1 / 3, and April age one is 4 / 5. April age two ends June 30, after the cutoff, and remains unobserved.

With the summary columns in the order above, the retention formula in F2 is:

=IF(OR(D2<>C2,C2=0),"",E2/C2)

Preserve full-precision percentages for the report. On the import preview, check that immature retention cells appear as null or blank rather than text or zero.

Build the Power BI matrix from the checked summary

In Power BI Desktop, select Get data, choose Excel, open the workbook, select the summary table in Navigator, and select Load. Microsoft's file connection quickstart documents this workbook import path.

Add a Matrix visual. Put entry cohort in Rows, age in Columns, and the prepared retention field in Values. Microsoft's matrix guide describes these field wells. Keep age as a category and sort it numerically so the columns follow customer age.

Use Average for the prepared retention field only because each visible cohort-age cell contains exactly one summary row. Do not add a segment or duplicate rows without rebuilding the counts and ratios at the new grain. Hide row and column grand totals; an average of displayed percentages is not the overall customer retention rate.

Format retention as a percentage. Apply background conditional formatting to the value cells and choose a visibly distinct treatment for blanks. Microsoft's conditional formatting documentation covers value-cell colors and empty-value settings. Display the cutoff and measure definition with the matrix.

Check totals before drawing conclusions

At age one, A, B, D, E, F, and G return: six customers from eight original members. The pooled rate is 6 / 8 = 75.0%. The simple mean of the March and April rates is 73.3%, which does not reconcile to the customer detail.

At age two, only the March cohort is mature: one return from three members, or 33.3%. Do not include April's five members in that denominator. Also do not sum original members across ages, because those rows repeat the same people.

Compare April with March at age one; April age two has no result yet. The retention-table interpretation guide explains how to read that unfinished diagonal. For subscription losses, use churn analysis in Power BI and its separate definition of churn.

Where it goes wrong

• Entry dates from a filtered order file. Existing customers become false new entrants.
• Order counts used as customer counts. Frequent buyers inflate retention.
• Inactive customers disappear. The denominator shrinks when an activity filter removes them.
• Blank future months become zeros. Missing observation becomes an apparent loss.
• Percentages receive totals. Adding or averaging cell rates hides their different denominators.

Covirage is not affiliated with Microsoft.

Check your own cohort export in Covirage

Upload your customer register and prepared activity summary; Covirage maps the columns, and its tools compute and check membership, completed windows, and retention counts. 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 self-service analytics for the supporting business workflow.