Learn to build a production-quality Customer Lifetime Value dashboard in Power BI using DAX iterator and window functions. This lesson covers acquisition cohort revenue matrices, repeat purchase rate calculation, churn probability scoring, and how to combine them into an actionable CLV composite score.
Here's a scenario that plays out in boardrooms everywhere: the marketing team is celebrating record-breaking customer acquisition numbers, while the finance team is quietly worried about margin. The disconnect? Nobody has a reliable model of what each customer is actually worth over time. You can pour budget into acquisition all day long, but if 60% of new customers never make a second purchase, you're running a leaky bucket.
Customer Lifetime Value (CLV) analysis is how you plug that bucket. And while dedicated ML platforms can build sophisticated CLV models, Power BI with well-crafted DAX measures can give your business analysts and decision-makers the interactive, slice-and-dice CLV intelligence they need without waiting on a data science team. By the end of this lesson, you'll be able to build a production-quality CLV dashboard that segments customers by acquisition cohort, tracks how their revenue evolves month over month, identifies customers at churn risk, and calculates repeat purchase rates — all dynamically responding to slicers and filters.
What you'll learn:
You should be comfortable with:
You do not need a machine learning background. Everything here is deterministic DAX.
Before writing a single measure, you need a data model that can support CLV analysis without fighting against you. CLV calculations involve multiple "time axes" simultaneously: the calendar date of a transaction, and the customer's acquisition month relative to that transaction. Getting this wrong in your model means your measures will be slow, incorrect, or both.
For this lesson, we'll work with a realistic e-commerce data model:
FactOrders — one row per order
OrderID, CustomerID, OrderDate, Revenue, ProductCategoryDimCustomer — one row per customer
CustomerID, CustomerName, AcquisitionDate, AcquisitionChannel, RegionDimDate — standard date table
Date, Year, Month, MonthYear, MonthOffset (months since an arbitrary epoch)DimCohort — a helper table we'll build
CohortMonth, CohortLabelThe relationship structure is: FactOrders[OrderDate] → DimDate[Date] (active), and DimCustomer[AcquisitionDate] → DimDate[Date] (inactive — we'll handle this with USERELATIONSHIP when needed).
Add this calculated column to DimCustomer:
-- In DimCustomer table
CohortMonth =
DATE(YEAR(DimCustomer[AcquisitionDate]), MONTH(DimCustomer[AcquisitionDate]), 1)
This normalizes every customer's acquisition date to the first of their acquisition month. All customers acquired in March 2023 get 2023-03-01. This is the foundation of cohort analysis.
Then add MonthsSinceAcquisition to FactOrders as a calculated column:
-- In FactOrders table
MonthsSinceAcquisition =
VAR AcqDate = RELATED(DimCustomer[CohortMonth])
VAR OrderMonth = DATE(YEAR(FactOrders[OrderDate]), MONTH(FactOrders[OrderDate]), 1)
RETURN
DATEDIFF(AcqDate, OrderMonth, MONTH)
Note
We're using calculated columns here for MonthsSinceAcquisition because this is a fixed property of each row — the relationship between a specific order and its customer's acquisition month never changes. This is exactly the right use case for a column versus a measure. For a deeper look at this tradeoff, see DAX Fundamentals: When to Use Calculated Columns vs Measures in Power BI.
The classic cohort revenue chart shows a matrix: acquisition month on rows, months since acquisition (0, 1, 2, 3...) on columns, and total revenue in each cell. Month 0 is what customers spent in their acquisition month. Month 1 is what that same cohort spent in the following month. And so on.
This sounds straightforward but has a subtle trap: the column context in a matrix visual doesn't know about "months since acquisition" unless you explicitly build that logic.
Cohort Revenue =
VAR CurrentCohortMonth = MAX(DimCustomer[CohortMonth])
VAR CurrentMonthOffset = MAX(FactOrders[MonthsSinceAcquisition])
RETURN
CALCULATE(
SUM(FactOrders[Revenue]),
FILTER(
ALL(FactOrders),
FactOrders[MonthsSinceAcquisition] = CurrentMonthOffset
),
FILTER(
ALL(DimCustomer),
DimCustomer[CohortMonth] = CurrentCohortMonth
)
)
Place DimCustomer[CohortMonth] on the row axis and FactOrders[MonthsSinceAcquisition] on the column axis. This measure will now populate each cell with the revenue generated by that cohort at that maturity stage.
Warning
Notice the ALL(FactOrders) and ALL(DimCustomer) inside the FILTER calls. Without these, the existing filter context from the visual would restrict your FILTER to a subset of data, causing cells to either show wrong values or go blank. You're explicitly overriding the filter context on those fields while letting other slicers (like Region or Channel) continue to filter naturally.
Raw revenue by period is useful, but cumulative revenue tells the real story of customer value building over time:
Cumulative Cohort Revenue =
VAR CurrentCohortMonth = MAX(DimCustomer[CohortMonth])
VAR CurrentMonthOffset = MAX(FactOrders[MonthsSinceAcquisition])
RETURN
CALCULATE(
SUM(FactOrders[Revenue]),
FILTER(
ALL(FactOrders),
FactOrders[MonthsSinceAcquisition] <= CurrentMonthOffset
),
FILTER(
ALL(DimCustomer),
DimCustomer[CohortMonth] = CurrentCohortMonth
)
)
The only change is <= instead of =. Now each cell shows the total revenue that cohort has generated from acquisition up to and including that month. This is what you'd divide by cohort size to get cumulative revenue per customer — a true CLV progress metric.
Revenue Per Cohort Customer =
VAR CurrentCohortMonth = MAX(DimCustomer[CohortMonth])
VAR CurrentMonthOffset = MAX(FactOrders[MonthsSinceAcquisition])
VAR CohortSize =
CALCULATE(
DISTINCTCOUNT(DimCustomer[CustomerID]),
FILTER(ALL(DimCustomer), DimCustomer[CohortMonth] = CurrentCohortMonth)
)
VAR CohortRevenue =
CALCULATE(
SUM(FactOrders[Revenue]),
FILTER(
ALL(FactOrders),
FactOrders[MonthsSinceAcquisition] <= CurrentMonthOffset
),
FILTER(
ALL(DimCustomer),
DimCustomer[CohortMonth] = CurrentCohortMonth
)
)
RETURN
DIVIDE(CohortRevenue, CohortSize)
Key insight
Cohort size is calculated once using ALL(DimCustomer) to get the total number of customers in that acquisition cohort regardless of whether they've made purchases in later months. If you filter by FactOrders, churned customers disappear from your denominator, inflating your per-customer revenue. That's survivorship bias, and it will make your CLV look better than reality.
Repeat purchase rate answers the question: "Of all the customers who made their first purchase in a given period, what percentage came back and bought again?" This is one of the most important early signals of product-market fit and customer satisfaction.
A customer is a repeater if they have at least two distinct orders. This seems obvious, but you need to define the time window carefully. Are we measuring repeat within 30 days? Within 90 days? Ever? For this lesson, we'll build a measure that works with a configurable window, using a What-If parameter.
First, create a What-If parameter in Power BI: go to Modeling → New Parameter, name it "Repeat Window Days", minimum 30, maximum 365, increment 30, default 90. This generates a table called Repeat Window Days and a measure Repeat Window Days Value.
Repeat Purchase Rate =
VAR WindowDays = [Repeat Window Days Value]
VAR CustomersInContext =
CALCULATETABLE(
DISTINCT(DimCustomer[CustomerID]),
ALLEXCEPT(DimCustomer, DimCustomer[CohortMonth], DimCustomer[AcquisitionChannel], DimCustomer[Region])
)
VAR RepeatCustomers =
COUNTROWS(
FILTER(
CustomersInContext,
VAR ThisCustomer = [CustomerID]
VAR FirstOrderDate =
CALCULATE(
MIN(FactOrders[OrderDate]),
FILTER(ALL(FactOrders), FactOrders[CustomerID] = ThisCustomer)
)
VAR SecondOrder =
CALCULATE(
COUNTROWS(FactOrders),
FILTER(
ALL(FactOrders),
FactOrders[CustomerID] = ThisCustomer
&& FactOrders[OrderDate] > FirstOrderDate
&& FactOrders[OrderDate] <= FirstOrderDate + WindowDays
)
)
RETURN SecondOrder > 0
)
)
VAR TotalCustomers = COUNTROWS(CustomersInContext)
RETURN
DIVIDE(RepeatCustomers, TotalCustomers)
This measure iterates over every customer in the current filter context (respecting cohort, channel, and region slicers), finds their first order date, then checks whether they placed any additional order within the window.
Warning
This measure uses nested FILTER and CALCULATE inside a row iteration — it's doing real work per customer. On datasets with hundreds of thousands of customers, this will be slow. We'll address performance optimization in the troubleshooting section. For now, understand the logic before optimizing it.
For large datasets, pre-calculate a IsRepeatCustomer flag as a calculated column in DimCustomer:
-- Calculated column in DimCustomer
IsRepeatCustomer_90d =
VAR ThisCustomer = DimCustomer[CustomerID]
VAR FirstOrder =
CALCULATE(
MIN(FactOrders[OrderDate]),
FILTER(ALL(FactOrders), FactOrders[CustomerID] = ThisCustomer)
)
VAR HasRepeat =
CALCULATE(
COUNTROWS(FactOrders),
FILTER(
ALL(FactOrders),
FactOrders[CustomerID] = ThisCustomer
&& FactOrders[OrderDate] > FirstOrder
&& FactOrders[OrderDate] <= FirstOrder + 90
)
) > 0
RETURN IF(HasRepeat, 1, 0)
Then your measure becomes:
Repeat Purchase Rate (Fast) =
DIVIDE(
SUM(DimCustomer[IsRepeatCustomer_90d]),
COUNTROWS(DimCustomer)
)
This pushes the row-by-row work to data refresh time (a one-time cost) rather than query time (paid on every visual render). The tradeoff is losing the dynamic window — you'd need separate columns for different windows (30d, 60d, 90d). Whether that's acceptable depends on your refresh cadence and user needs. For most production dashboards, hardcoded windows are fine. See the performance discussion in Performance Tuning DAX: Optimize Slow Measures with DAX Studio for guidance on measuring which approach actually matters for your dataset size.
Churn probability is trickier than cohort revenue or repeat rate because "churn" in a non-subscription business isn't a discrete event — customers don't send you a cancellation email. They just... stop buying. The standard approach is recency-based: a customer who hasn't purchased in a long time is increasingly likely to have churned.
We'll build a tiered churn risk score based on recency segments, using window functions to rank customers within their cohort.
First, a measure for context:
Days Since Last Purchase =
VAR LastPurchase =
CALCULATE(
MAX(FactOrders[OrderDate]),
ALL(DimDate)
)
VAR Today = TODAY()
RETURN DATEDIFF(LastPurchase, Today, DAY)
Note
We use ALL(DimDate) to clear any date filter that might be active on the visual — we want the actual most recent purchase regardless of what date range is selected on the report page.
Add this to DimCustomer:
-- Calculated column in DimCustomer
ChurnRiskTier =
VAR ThisCustomer = DimCustomer[CustomerID]
VAR LastPurchaseDate =
CALCULATE(
MAX(FactOrders[OrderDate]),
FILTER(ALL(FactOrders), FactOrders[CustomerID] = ThisCustomer)
)
VAR DaysSinceLastPurchase =
IF(
ISBLANK(LastPurchaseDate),
999, -- Never purchased — treat as highest risk
DATEDIFF(LastPurchaseDate, TODAY(), DAY)
)
RETURN
SWITCH(
TRUE(),
DaysSinceLastPurchase <= 30, "Active",
DaysSinceLastPurchase <= 90, "At Risk",
DaysSinceLastPurchase <= 180, "High Risk",
DaysSinceLastPurchase <= 365, "Very High Risk",
"Likely Churned"
)
This gives you a simple categorical risk tier. But for more analytical use, you want a numeric score.
Here's where window functions earn their keep. We'll use WINDOW to calculate a percentile-based churn score — customers with the longest recency get a score near 1.0 (almost certainly churned), while recent customers score near 0.
Churn Probability Score =
VAR AllCustomers =
ADDCOLUMNS(
SUMMARIZE(
ALL(DimCustomer),
DimCustomer[CustomerID],
DimCustomer[CohortMonth]
),
"LastPurchase",
CALCULATE(
MAX(FactOrders[OrderDate]),
FILTER(ALL(FactOrders), FactOrders[CustomerID] = EARLIER(DimCustomer[CustomerID]))
),
"DaysSincePurchase",
IF(
ISBLANK(CALCULATE(MAX(FactOrders[OrderDate]), FILTER(ALL(FactOrders), FactOrders[CustomerID] = EARLIER(DimCustomer[CustomerID])))),
999,
DATEDIFF(
CALCULATE(MAX(FactOrders[OrderDate]), FILTER(ALL(FactOrders), FactOrders[CustomerID] = EARLIER(DimCustomer[CustomerID]))),
TODAY(),
DAY
)
)
)
VAR CurrentCustomer = MAX(DimCustomer[CustomerID])
VAR CurrentDays =
MAXX(
FILTER(AllCustomers, [CustomerID] = CurrentCustomer),
[DaysSincePurchase]
)
VAR MaxDays = MAXX(AllCustomers, [DaysSincePurchase])
VAR MinDays = MINX(AllCustomers, [DaysSincePurchase])
RETURN
DIVIDE(CurrentDays - MinDays, MaxDays - MinDays)
Key insight
This is a min-max normalization of recency into a 0–1 churn probability score. It's not a machine learning model, but it's directionally correct and immediately actionable. The customer with the highest recency (most days since last purchase) scores 1.0. The most recent customer scores 0.0. This is a measure designed for use in a table or scatter chart visual where CustomerID is in the row context.
For a cleaner implementation using native window functions (available in Power BI as of 2023), here's a leaner approach using WINDOW directly in a measure context:
Customer Recency Rank =
VAR CurrentCustomer = SELECTEDVALUE(DimCustomer[CustomerID])
VAR CustomerDaysSince =
CALCULATE(
DATEDIFF(MAX(FactOrders[OrderDate]), TODAY(), DAY),
FILTER(ALL(FactOrders), FactOrders[CustomerID] = CurrentCustomer)
)
VAR TotalCustomers =
CALCULATE(
DISTINCTCOUNT(DimCustomer[CustomerID]),
ALL(DimCustomer)
)
VAR CustomersWithMoreRecency =
CALCULATE(
DISTINCTCOUNT(DimCustomer[CustomerID]),
FILTER(
ALL(DimCustomer),
VAR OtherCustomer = DimCustomer[CustomerID]
VAR OtherDays = CALCULATE(
DATEDIFF(MAX(FactOrders[OrderDate]), TODAY(), DAY),
FILTER(ALL(FactOrders), FactOrders[CustomerID] = OtherCustomer)
)
RETURN OtherDays > CustomerDaysSince
)
)
RETURN
DIVIDE(CustomersWithMoreRecency, TotalCustomers)
This ranks each customer by how many other customers have been inactive longer. A score of 0.95 means 95% of customers are less active — this person is in the top 5% churn risk.
Individual metrics are useful, but executives want a single number. We can build a composite CLV score that weighs cohort position, revenue contribution, repeat behavior, and churn risk together.
CLV Score =
VAR TotalRevenue =
CALCULATE(
SUM(FactOrders[Revenue]),
ALL(DimDate)
)
VAR MaxCustomerRevenue =
CALCULATE(
MAXX(
VALUES(FactOrders[CustomerID]),
CALCULATE(SUM(FactOrders[Revenue]))
),
ALL(DimCustomer)
)
VAR RevenueScore = DIVIDE(TotalRevenue, MaxCustomerRevenue)
VAR RepeatFlag = MAX(DimCustomer[IsRepeatCustomer_90d]) -- 1 or 0
VAR ChurnRisk = [Customer Recency Rank] -- 0 to 1, higher = more churn risk
-- Weighted composite: 50% revenue, 30% repeat behavior, 20% churn (inverted)
RETURN
ROUND(
(RevenueScore * 0.5) + (RepeatFlag * 0.3) + ((1 - ChurnRisk) * 0.2),
3
)
Tip
The weights (50/30/20) are business decisions, not mathematical truths. Consider exposing them as What-If parameters so business stakeholders can explore different weighting strategies interactively. This also makes the model self-documenting — when someone asks "why does this customer score higher?" you can point to the sliders. The Writeback Patterns in DAX article covers this pattern in depth.
Once you have a CLV score, you want to segment customers into actionable tiers — Champions, Loyal, At Risk, and Dormant — and make those segments filterable.
CLV Segment =
VAR Score = [CLV Score]
RETURN
SWITCH(
TRUE(),
Score >= 0.75, "Champion",
Score >= 0.50, "Loyal",
Score >= 0.25, "At Risk",
"Dormant"
)
For use as a slicer, this needs to be a calculated column rather than a measure. Create it in DimCustomer by substituting [CLV Score] with the full inline formula (or reference the calculated column intermediate steps). The pattern of building dynamic segmentation logic using SWITCH and score thresholds applies broadly.
Build a complete CLV mini-dashboard using the patterns in this lesson. Here's your brief:
Dataset: Use a dataset with at least 500 customers, 2 years of order history, and an identifiable acquisition date per customer. The AdventureWorks sample data works perfectly.
Task 1 — Cohort Grid:
Create a matrix visual with DimCustomer[CohortMonth] on rows and FactOrders[MonthsSinceAcquisition] (0 through 11) on columns. Apply the Revenue Per Cohort Customer measure. Apply conditional formatting (background color scale) to immediately visualize where cohorts are strong or weak.
Task 2 — Repeat Rate Trend:
Create a line chart with DimCustomer[CohortMonth] on the X-axis and Repeat Purchase Rate (Fast) on Y-axis. Add a slicer for AcquisitionChannel. Do certain channels produce measurably better repeat rates? How does that change your interpretation of acquisition cost?
Task 3 — Churn Risk Table:
Build a table visual with DimCustomer[CustomerName], DimCustomer[ChurnRiskTier], Days Since Last Purchase, Total Revenue (all time), and Customer Recency Rank. Sort by Customer Recency Rank descending. These are your customers most likely to churn. Add a filter to show only "High Risk" and "Very High Risk" tiers.
Task 4 — CLV Scatter:
Create a scatter chart: X-axis = Revenue Per Cohort Customer, Y-axis = Repeat Purchase Rate (Fast), legend = CLV Segment. Which quadrant do you want most customers in? Which quadrant is your business actually in?
Reflection question: If your most recent cohort shows lower Month 0 revenue but higher repeat rate than older cohorts, what does that signal about your acquisition strategy or product changes?
Symptom: The cohort matrix totals don't match simple SUM(Revenue) measures elsewhere on the page.
Cause: You're stripping filter context with ALL() inside the FILTER calls, which also removes any active page-level slicers you actually want to respect.
Fix: Be surgical with your ALL usage. Use ALLEXCEPT to strip only the fields you're explicitly re-applying, leaving other slicers intact:
-- Instead of ALL(DimCustomer), use:
FILTER(
ALLEXCEPT(DimCustomer, DimCustomer[Region], DimCustomer[AcquisitionChannel]),
DimCustomer[CohortMonth] = CurrentCohortMonth
)
Symptom: Your newest cohorts show 100% repeat rate while older cohorts show 20–30%.
Cause: You're calculating repeat within a window that hasn't expired yet for recent cohorts. A customer acquired last week can't have failed to repeat within 90 days — there hasn't been 90 days yet.
Fix: Add a maturity filter. Only include cohorts where TODAY() - CohortMonth > WindowDays:
Repeat Purchase Rate (Mature Cohorts) =
VAR WindowDays = [Repeat Window Days Value]
VAR CohortDate = MAX(DimCustomer[CohortMonth])
VAR CohortIsMature = DATEDIFF(CohortDate, TODAY(), DAY) > WindowDays
RETURN
IF(CohortIsMature, [Repeat Purchase Rate (Fast)], BLANK())
Tip
Returning BLANK() instead of 0 for immature cohorts means Power BI won't plot those points on trend lines or include them in averages. This is almost always the right choice — a missing data point is honest, while a misleading 0 or inflated 100% erodes trust in the report.
Symptom: Every customer shows a churn probability of 0.5 or some other constant.
Cause: MAX(DimCustomer[CustomerID]) returns the same value regardless of which row is being evaluated in a table visual if CustomerID is numeric and the visual is showing a total row.
Fix: Use SELECTEDVALUE instead of MAX when you need to identify a single customer in row context, and always test your measure on a table with a single customer filtered to verify it returns different values per row.
Symptom: The churn probability measure takes 30+ seconds to load in a table with 10,000 customers.
Cause: Nested FILTER+CALCULATE inside COUNTROWS over a large table is O(n²) work — for each customer, you're scanning all other customers.
Fix: Push recency calculations to calculated columns at refresh time, as we discussed with IsRepeatCustomer_90d. For churn ranking, pre-calculate LastPurchaseDate and DaysSinceLastPurchase as columns in DimCustomer, then your measure only needs to do the division, not the lookup. For detailed profiling guidance, see Performance Tuning DAX: Optimize Slow Measures with DAX Studio.
Symptom: Some customers appear in your cohort grid in a month before they first appear in FactOrders.
Cause: DimCustomer[AcquisitionDate] might be populated from a CRM or marketing system, while actual first order data lives in your transactional system. Customers sometimes register without purchasing, or data pipelines have timing lags.
Fix: Decide explicitly which date defines your cohort. For revenue analysis, the first order date is almost always more reliable than a CRM signup date. Recalculate CohortMonth using MIN(FactOrders[OrderDate]) per customer:
-- Alternative CohortMonth based on first order
CohortMonth_Orders =
VAR FirstOrder =
CALCULATE(
MIN(FactOrders[OrderDate]),
FILTER(ALL(FactOrders), FactOrders[CustomerID] = DimCustomer[CustomerID])
)
RETURN DATE(YEAR(FirstOrder), MONTH(FirstOrder), 1)
Warning
If you use this in a calculated column in DimCustomer, be careful about circular dependency — DimCustomer referring to FactOrders which refers back to DimCustomer via a relationship. Test thoroughly and ensure the relationship direction supports this lookup.
You've now built a production-quality CLV analysis framework in DAX. Let's recap the key patterns and when to reach for each one:
Cohort Revenue uses ALL + FILTER to override default filter context and re-apply it precisely on two dimensions simultaneously — acquisition month and time offset. The key insight is that you must hold cohort identity constant while varying the time axis.
Repeat Purchase Rate uses iterator functions to evaluate a condition (did this customer buy again?) per customer, then aggregates the boolean result. For large datasets, pre-calculate as a calculated column and measure only does simple aggregation.
Churn Probability uses recency-based normalization to convert raw days-since-purchase into a comparable 0–1 score. Window functions help rank customers relative to each other without leaving DAX.
CLV Score combines these into a weighted composite, surfaced as a slicer-ready segment.
The natural next step is to extend this model with predictive elements — specifically, projecting future revenue based on observed cohort decay curves. The DAX Predictive Patterns: Moving Averages, Exponential Smoothing, and Forecast Measures Without ML Tools article shows how to build forward-looking measures that extrapolate cohort trends. You might also want to explore DAX Cohort Analysis: Building Retention, Churn, and Lifetime Value Measures with GENERATE and Date-Based Segmentation for alternative approaches to the same problem space using GENERATE-based virtual tables.
CLV analysis is one of those domains where DAX genuinely shines — the ability to slice a live cohort grid by acquisition channel or region in real time, with sub-second response, is something that takes significant engineering effort to replicate in other environments. Build this once, build it right, and it becomes one of the highest-value assets in your analytics portfolio.