Regular customers

[ISV]

Hello,

I cant understand how to find regular customers.
- there are lines with orders within 5 years

- there are email and phone numbers in these orders

How to understand which customers are regular? For example, created repeated sales by year.

Thank you for help.

[Anonymous · Accepted solution]

To find out if a customer is regular, I'd create a column in your customer table and use an if statement to check if they have any orders in the last 5 years.  For my example i'm going to say "5 orders in the last 5 years is regular"

IsRegular = IF(
	Calculate(
		count('Invoices'[InvoiceID]),
		all('Invoices'),
		'Invoices'[CustomerID] = 'Customers'[CustomerID],
		DateDiff('Invoices'[Date], Today(), YEAR) <= 5
	) >= 5,
	TRUE(),
	FALSE()
)

(i've assumed some table names and fields)

From here, if you wanted their names and phone numbers you can do up a matrix of the Invoice table filtering out where IsRegular = False. Then you could set the Date filter to be in the last 5 years.