So hopefully I can explain this and make sense. So what I want to do is see if a customer made a purchase in the same year they joined the rewards program. So lets say Rewards Join Date is 01/04/2018 and their first purchase was 08/26/2018. What I would like to be able to do is if the purchase date is in the same year as the Rewards Join Date then return the response in a column that says “Purchase in Rewards Join Year.”
Would the basis of this be something like SWITCH TRUE()?
If so I know about using “&&” but how would I go about saying between two dates for Rewards Join Date and then doing it again for their First Purchase?
Greetings,
You can try doing some calculated columns on your Customer Table.
Is your Rewards Join Date on your Customer Table already or another table?
First you can add the following column to bring in your first purchase per customer
First Purchase =
CALCULATE(
FIRSTDATE( ‘Purchases’[Purchase Date] ),
FILTER(‘Purchases’,‘Purchases’[Customer#]=’*Customers’[Customer#])
)
In the last part of the filter when using what you gave me it will only give me the option of selecting something from the Purchases table or using a Measure. I would need the ID from another table that is in an inactive relationship.
I have a key between the tables because there are multiple tables and this was the only way to avoid having bi-direction between all of the times. The key is a table of all customer IDs taken from the various tables.
The tables are
Rewards Join
Purchases
Customer feedback
Customer Preferred Store
Where the customer ID is the one common thing among all tables. But I created a unique Customer key that contains all IDs.
I was thinking it would be USERELATIONSHIP OR TREATAS.
I believe you could then do these Calculated Columns on that Customer key table since it has relationships with all tables needed and it won’t complicate things having to do UseRelationship.
It will still have good functionality allowing you to bring those dates into a tables/matrix/chart because they are still very unique dates of only 1 Reward Date and 1 First Purchase date per Customer Key. You should be able to just adjust that previous formula to be on the Customer Key table and filtering by the Customer Key in the other table to = the Customer Key in that Customer Key table.
