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 ...
  • 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.