Forum Discussion

ISV's avatar
ISV
New Member
9 years ago
Solved

Regular customers

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.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.