How to create a recurring sales indicator in DAX: step by step
This tutorial shows how to create a recurring sales indicator in DAX to identify customers who buy regularly — a very useful metric for segmentation, retention and cross-sell campaigns. We’ll first explain why: knowing which customers have recurring behavior helps prioritize retention investments, estimate predictable revenue and measure the impact of loyalty initiatives. Then, we provide a practical step-by-step example that includes calculation of recency, frequency and a recurrence window; all designed to be easy to adapt to your business.
Prerequisites
- Power BI Desktop with a simple model: Sales table (CustomerID, OrderDate, SalesAmount) and Customers table (CustomerID). You can also include OrderID if you have detailed transactions, but it’s not required.
- Basic knowledge of DAX: measures, CALCULATE, FILTER, ALL and RELATED are not required to follow this tutorial, but it’s useful to know the difference between a measure and a column.
- A date table (Calendar) marked as Date table and related to Sales[OrderDate]. This allows using time intelligence functions like DATESINPERIOD. The Calendar should cover at least the historical period you want to analyze (e.g.: 24 months).
Step 1: think about what “recurring” means
Before writing DAX it’s important to define the business rule. There is no universal definition of “recurring”: it depends on the product’s purchase cycle. A simple rule: a customer is recurring if they have at least N purchases in the last M months and the last purchase was in the last X days. For example, N=3 purchases in M=6 months and last order in the last X=30 days is a reasonable definition for monthly consumption/subscription services. For less frequent product purchases you might use N=2 in M=12 months and X=90 days. Define these parameters with stakeholders: marketing and sales may accept trade-offs (more sensitive = larger recurring base; stricter = smaller, but more reliable). It’s common to validate with samples: expect 10–25% of the base to be marked as recurring — if it’s 70% the rule is probably too permissive.
Step 2: create basic support measures
We will create simple measures that help interpret and debug the result. Support measures make it easier to test with concrete examples and allow creating additional segments (e.g.: inactive customers).
Sales Count = COUNTROWS(Sales)
Last Purchase Date = MAX(Sales[OrderDate])
These measures show the total number of sales rows by context and the last purchase date per customer. In a table with Customers[CustomerID] you can quickly check whether the Last Purchase Date measure matches reality, and whether Sales Count reflects transactions or documents (if 1 sale per OrderID, Sales Count may be equivalent to number of orders).
Step 3: count purchases in the last M months
Use CALCULATE with DATESINPERIOD to count transactions within the M-month window from the current context date. This measure returns the frequency in the chosen period and should be tested with different values of M (3, 6, 12 months).
Purchases Last 6 Months =
CALCULATE(
COUNTROWS(Sales),
DATESINPERIOD(
Calendar[Date],
MAX(Calendar[Date]),
-6,
MONTH
)
)
Practical example: in a base of 50,000 customers, you might expect 4,000 to have at least 1 purchase in the last 6 months; if 1,200 have ≥3 purchases, that gives 3% of the base as recurring according to N=3/M=6. Adjust according to results and business expectations.
Step 4: calculate days since last purchase (recency)
Recency is critical: a customer may have purchased a lot in the past but be inactive today. This measure calculates how many days have passed since the last purchase up to the report’s context date (useful with a date slicer).
Days Since Last Purchase =
VAR LastDate = CALCULATE(MAX(Sales[OrderDate]), ALL(Calendar))
VAR TodayContext = MAX(Calendar[Date])
RETURN
DATEDIFF(LastDate, TodayContext, DAY)
If you use a fixed analysis date (for example, 2026-10-31), a customer with last purchase on 2026-10-15 will have Days Since Last Purchase = 16. Typical cutoffs: X=30 for monthly purchases; X=90 for quarterly/annual purchases.
Step 5: create the final measure "Is Recurring Customer"
Now we combine the frequency and recency conditions. We should also consider edge cases: customers with no purchases should result in 0 (or FALSE). We return 1/0 to simplify aggregations (SUM to count recurrents).
Is Recurring Customer =
VAR MinPurchases = 3
VAR WindowMonths = 6
VAR MaxDaysSince = 30
VAR PurchasesInWindow =
CALCULATE(
COUNTROWS(Sales),
DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -WindowMonths, MONTH)
)
VAR DaysSince =
VAR LastDate = CALCULATE(MAX(Sales[OrderDate]), ALL(Calendar))
RETURN DATEDIFF(LastDate, MAX(Calendar[Date]), DAY)
RETURN
IF(PurchasesInWindow >= MinPurchases && DaysSince <= MaxDaysSince, 1, 0)
Practical notes: adjust MinPurchases, WindowMonths and MaxDaysSince with business parameters. To answer marketing questions, use this measure in cards (e.g.: SUM(Is Recurring Customer) shows the total recurring customers in the period).
Step 6: use the indicator in the report
Drag Customers[CustomerID] into a table in Power BI and add the Is Recurring Customer measure. Create a slicer by Calendar[Date] to simulate different analysis dates and validate the behavior. To summarize, use SUM(Is Recurring Customer) or COUNTROWS with a FILTER: for example, a card with SUM(Is Recurring Customer) will show how many customers are recurring in that time context.
Verify the result
Confirm the indicator works by checking known examples: choose a customer with 4 purchases in the last 6 months and last purchase 10 days ago — it should return 1. Test customers with single purchases or last purchase 90 days ago — it should return 0. Also validate with samples: check 20 customers manually and calculate the percentage that matches the automatic classification; if the error rate is >10% you probably need to adjust the definition. Another useful check is to compare average revenue of recurrents vs non-recurrents: expect recurrents to have higher CLV.
Conclusion
With these measures you have a simple and interpretable recurring customer indicator in DAX. Next steps: convert Is Recurring Customer into a calculated column for historical analysis, create segments (high/medium/low recurrence) with SWITCH, or incorporate SalesAmount to measure average recurring revenue. Final tip: always test with real examples in your model and document the definition (N/M/X) so all teams apply the same logic. Which rules do you find most useful for your case?