Customer analytics patterns

01The problem

Kolanut's sales reps are judged on keeping customers ordering. The sales director wants a page that answers, for any month she picks:

• How many customers are active: they ordered in the last 30 days?
• How many have lapsed: they've ordered before, but not in the last 30 days?
• Which customers make up the 80% of revenue we can't afford to lose?

These are some of the most requested measures in any business with repeat customers (distributors, banks, telecoms, schools collecting fees), and they all follow a few patterns. Learn the patterns once and you can build them on any data.

02The concept

Pattern 1: a rolling window from the selected date

"As of" the last date in the current filter, look back a fixed number of days:

Active Customers 30d =
VAR LastDay = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( orders[customer_id] ),
        DATESINPERIOD ( 'Date'[Date], LastDay, -30, DAY )
    )

At June 2026, LastDay is 30 June and the window is 1 to 30 June.

Pattern 2: everything up to a date

"Ever ordered by the end of the period" needs every date up to LastDay. Remove the date filters explicitly, then add the condition:

Customers to Date =
VAR LastDay = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( orders[customer_id] ),
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] <= LastDay
    )

Lapsed Customers 30d = [Customers to Date] - [Active Customers 30d]

REMOVEFILTERS ( 'Date' ) matters: without it, a filter on Date[Year Month] from the visual would still be in place, and "to date" would mean "this month only".

The same pattern gives headcount on a date in HR data: employees hired on or before the date, who haven't left by it.

Pattern 3: cumulative share (Pareto)

Rank customers by revenue, then add up everyone whose revenue is at least the current customer's:

Cumulative Share =
VAR CurrentRevenue = [Revenue]
VAR AllCustomers = ALLSELECTED ( customers[customer_name] )
VAR RunningTotal =
    SUMX ( FILTER ( AllCustomers, [Revenue] >= CurrentRevenue ), [Revenue] )
RETURN
    DIVIDE ( RunningTotal, CALCULATE ( [Revenue], AllCustomers ) )

ABC Class =
SWITCH (
    TRUE (),
    ISBLANK ( [Revenue] ), BLANK (),
    [Cumulative Share] <= 0.8, "A",
    [Cumulative Share] <= 0.95, "B",
    "C"
)

Class A customers together make up the first 80% of revenue. They get the most attention from account managers.

03Example

The sales director's page for June 2026, by channel:

Every wholesaler ordered in June; 15 of 39 kiosks didn't. Before you send the kiosk list to the sales reps, check the window. Kiosks order small amounts, less often, and over the last three months (DATESINPERIOD with -3, MONTH), all 90 customers ordered. A 30-day window suits wholesalers; for kiosks, 60 days or three months may be the fairer test of "lapsed". The pattern is the same, so make the window a parameter and let the business choose.

04Walkthrough

1. Add Active Customers 30d, Customers to Date and Lapsed Customers 30d. Build the table from the example, with a slicer on Date[Year Month] set to 2026-06.
2. Remove REMOVEFILTERS ( 'Date' ) from Customers to Date and watch "to date" collapse to June only. Put it back.
3. Change -30 to -60. Active customers at June 2026 rise to 84.
4. Add Cumulative Share and ABC Class to a table of customers[customer_name] and [Revenue], sorted by revenue, with no date filter. Find the row where the share first passes 80%.

05Practice

06More practice

07Check your understanding