Forum Discussion
Helper IINew and Returning Customers
Hello everyone, I am trying to get my data in a way that would allow me to flag individuals as New, Returning, and Infrequent. The chart below shows what I would like the data to look like, however at this time I only have the Mail # and Year columns. While the example does not show any "duplicate" mail #s or years the acutal data does maintain dublicates that should not be removed.
The flag logic should work like this...
The first year a Mail # appears they are considered New, and recieve a 1.
The Second year a Mail # appears within a five year period they are no longer considered New and are now considered Infrequent, so they have a 0 for the New column, and a 1 for the Infrequent column.
The Third year a Mail # appears within a five year period they remain as Infrequent and get a 1 in the Infrequent column.
The Fourth Year a Mail # appears within a five year period they remain as Infrequent and get a 1 in the Infrequent column.
The Fifth Year a Mail # appears within a five year period they are no longer considered Infrequent and are now considered Returning, so they have a 0 for the Infrequent column, a 0 for the New column and a 1 for the Returning column.
If a Mail # appears each year for more than five consecutive years they recieve a 1 in the Returning column for each of the years after year five.
If the customer misses a year once they are recieving a 1 in the Return column they will stop recieving a Returning designation and revert back to Infrequent.
Is this logic even possible for BI? I have spent weeks trying to figureout how to do this with no luck.
Thanks in advance!
3 Replies
v-yuta-msftCommunity Support
chq ,
Basically, you can create a calculate column based on Year in each Mail #:
Rank = RANKX ( FILTER ( Table, Table[Mail #] = EARLIER ( Table[Mail #] ) ), Table[Year], , ASC, DENSE )Then you can create a measure using switch statement like below:
New = SWITCH ( TRUE (), Table[Rank] = 1, 1, Table[Rank] > 1, 0 )
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chqOkay I see how that enables me to determine "new", but how does that show me returning or infrequent columns?
- chq
When I plug those formulas in this is what I am ending up with....
That does not seem to properly fit the logic statements that I need to apply to my data as outlined in my first post. I guess I am not sure how those formulas you provided help categorize new, infrequent, or returning guests. Can you offer additional information?