Power BI cohort analysis that shows whether customers stay
Power BI cohort analysis groups customers by when they first bought and then follows each group month by month. It shows whether newer customers stay longer than older ones did, which a single retention figure hides. Our consultants build the cohort model inside your own Microsoft tenant, and your team keeps the file and the measure notes.
- Groups by first purchase
- Retention month by month
- Heat map view
Last updated
What does Power BI cohort analysis show that totals hide?
Active customer counts can rise while the business leaks. New sign-ups cover for the people who leave. So the total looks healthy until acquisition slows, and then the fall comes as a shock.
A cohort view separates the generations. A cohort is a group of customers who share a starting point, usually the month of their first purchase. Each row of the grid follows one cohort. Each column shows a later month, counted from the start.
Reading down a column answers a sharp question. For example, how many customers are still buying three months in, and is that share better for recent cohorts? If it improves, your product or onboarding changes work. Otherwise growth rests on marketing spend alone.
Cohort measures and their standard meaning
Each measure works on the grid of cohort month against months since first purchase.
Cohort size
Count the distinct customers whose first purchase fell in the cohort month. Every rate divides by this number.
Retention rate
Divide the cohort members active in a given later month by the cohort size.
Churned customers
Subtract active members from cohort size. However, agree first how long a silence counts as churn.
Revenue retention
Compare a cohort's revenue in a later month with its revenue in the first month, since spend can grow while numbers shrink.
Cumulative value per customer
Add up the cohort's revenue to date and divide by cohort size. Then payback on acquisition cost becomes visible.
Average months active
Average how many months each member placed an order, so you can compare cohorts with one number.
How do you build cohorts in a Power BI model?
Two pieces of data do the work. First, every customer needs a cohort label. We add a column to the customer table that holds the month of the first order. Because it is a column, it works on the rows of a matrix.
Second, every order needs a period number: the count of months between the cohort month and the order month. Month zero is the first purchase. We store that number on the sales table or in a small lookup. Then the matrix puts it on the columns.
The retention measure counts distinct customers in each cell and divides by the cohort size. Conditional formatting turns the grid into a heat map, so weak cohorts show at a glance. Recent cohorts have fewer columns, of course, and the triangle shape is normal. Teams that want help with that measure itself can use our power bi dax consulting service.
Mistakes that spoil Power BI cohort analysis
Six slips account for most cohort grids that mislead.
- Dividing by customers active last month instead of cohort size
- Letting a page filter change a customer's first purchase date
- Comparing a young cohort's part month with full months
- Using calendar months when the business runs on weekly cycles
- Mixing one-time buyers and subscribers in one grid
- Reading tiny cohorts as if they were reliable
How we set up Power BI cohort analysis
The definition of an active customer comes first, because the whole grid depends on it.
Define active and first purchase
First we agree which transactions count and how to treat refunds, trials and merged accounts.
Add cohort and period fields
We create the cohort month on the customer table and the period number on the orders.
Write and verify the measures
Next we check several cohorts by hand against raw orders, so the grid earns trust.
Design the grid and curves
Finally we build the heat map, the retention curves and a segment comparison, then hand over with notes.
Cohorts in Power BI: questions from growth teams
Which visual suits Power BI cohort analysis best?
The matrix visual suits it best. Cohort months go on the rows, months since first purchase go on the columns and the retention rate fills the cells. Conditional formatting then adds a color scale. In addition, a line chart with one line per cohort shows the curves, which some readers find easier.
Can cohort analysis in Power BI use something other than first purchase?
Yes. Any shared starting event can define a cohort: sign-up date, first login, contract start or the campaign that brought the customer in. We can also offer a choice of cohort type on the page. However, each definition needs its own column, so we add only the ones you will use.
Does it work for businesses without subscriptions?
It does. For repeat purchase businesses, active simply means the customer ordered in the period. The grid then shows repeat buying rather than renewal. Still, buying cycles vary by trade. If customers normally reorder once a quarter, monthly columns look alarming, so we set the period length to match the real cycle.
How much history do we need?
Enough for several cohorts to age. A year of orders already gives a useful grid, and more history adds seasonal context. Yet the first cohort in your data may include older customers whose true first order came earlier. We flag that cohort, otherwise it looks stronger than it is.
What does working with you involve?
A free 30-minute assessment, then a written fixed scope, then weekly demos until handover. Power BI Agency is a division of Sataware Technologies, independent of Microsoft. Our small team works online inside your tenant. We never resell licenses, and everything we build is yours at handover.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.