Hi everyone,
I have a bit of a tricky query that I’m getting an error with.
GB Test.pbix (108.7 KB)
I’m trying to create a Calculated table to show customers who have made purchases within the last 90 days.
I’m trying to alter the code below to include a few more rules and filters without the need to use slicers etc:
First, to put this code between a certain period (01/03/2024 to 31/05/2024 for example).
Second, to only include a certain product description (Iphone 15 or item code 5 only for example or any other product).
Third, to exclude STAFF from the Customer Class field in the Customer table.
Last, to only include Tesco from the Chain field in the customer table.
I know you can filter and use slicers, etc but my company are looking for set code that helps to not require slicers etc for the business users so they can see the data straight away.
The code I used below is getting an error - Too many arguments were passed to the AND function. The maximum argument count for the function is 2.
Any idea how to fix this? My code is below and sample PBIX also.
Any help would be greatly appreciated!
Customers with first purchase 90 Days =
FILTER(
ADDCOLUMNS(
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE(
‘Sales Table’,
‘Sales Table’[Customer ID],
“MinTransactionDate” , MIN( ‘Sales Table’[Transaction Date]) ,
“MaxTransactionDate” , MAX(‘Sales Table’[Transaction Date])
),
"1stTransactionOrNot" ,
IF( [MinTransactionDate] = [MaxTransactionDate] ,
"1st Transaction" , "Not the first Transaction")
),
"DaysFromFirstTransaction" ,
IFERROR( INT( TODAY() - [MaxTransactionDate] ),
0)
),
"RegularCustomerOrNot" ,
IF( [DaysFromFirstTransaction] <= 90 ,
"Regular Customer" , "Not a regular Customer" )
),
AND( [RegularCustomerOrNot] = "Regular Customer" ,
NOT ISBLANK( 'Sales Table'[Customer ID] ),
'Sales Table'[Transaction Date] >= DATE(2024, 3, 1)
&& 'Sales Table'[Transaction Date] <= DATE(2024, 5, 31),
('Sales Table'[Product Description] = "Iphone 15"
|| 'Sales Table'[Item Code] = 5),
'Customer Table'[Customer Class] <> "STAFF", 'Customer Table'[Chain] = "Tesco" ) )
Before you send your question.
Make sure that all details relevant to your question is complete:
- Your current work-in-progress PBIX file - VERY IMPORTANT
- A clear explanation of the problem you are experiencing
- A mockup of the results you want to achieve
- Your underlying data file
Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions
Also make sure that your data file contains no confidential information. If it does, click the link above.
*** DELETE THIS MESSAGE IF YOU ARE SURE ALL YOUR DETAILS ARE COMPLETE OR IF THE ABOVE INFORMATION IS NOT APPLICABLE TO YOUR QUESTION.***
