Forum Discussion
Regular customers
- Anonymous9 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.
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.