Forum Discussion
Flagging a customer's second visit
Hello -
I have a table of raw sales transaction data (one line per item, multiple lines for a transaction, with a unique transaction code for each transaction).
| Date | Transaction code | Customercode | Storecode | Item code | Quantity |
| 1-Jan | 100 | 1000 | 1 | ABC | 2 |
| 1-Jan | 100 | 1000 | 1 | XYZ | 3 |
| 3-Jan | 101 | 1001 | 2 | ABC | 2 |
| 4-Jan | 102 | 1000 | 2 | XYZ | 4 |
| 5-Jan | 103 | 1002 | 1 | ABC | 1 |
| 5-Jan | 103 | 1002 | 1 | XYZ | 2 |
I would like to generate a calculated column that flags whether a particular transaction is the second time that particular customer has visited. In the above, TransactionCode 102 would be flagged as True since it is the second visit from customer 1000.
I think I need a DistinctCount of TransactionCodes filtered for both a customercode match, and a date filter limiting to past transactions, and if that DistinctCount adds to 1 then the flag is True, otherwise False.
For the date filter, calendar table is setup and linked so expect I need something along the lines of;
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] < MIN ( 'Calendar'[Date])
)
But cannot get the overall code to work with both filters.
Would appreciate assistance! Thank you.
6 Replies
Hi,
Try this calculated column formula
=CALCULATE(DISTINCTCOUNT(Data[Date]),FILTER(Data,Data[Customercode]=EARLIER(Data[Customercode])&&Data[Date]<EARLIER(Data[Date])))
Hope this helps.
Hello Ashish - thank you!
I've plugged that formula in but it is taking a very long time to calculate as a column formula (data is about 5 million rows, so that is a lot of filtering and counting going on). 20 minutes on an i7-7700k and it doesn't seem to be anywhere close to finished.
Any thoughts on how to make this work with a measure instead that could be less computationally intensive?
Hi,
In your original post, you mentioned that you wanted a calculated column formula. Now you want a measure. Share the link from where i can download your trimmed data (the file shize should only be a couple of MB's please) and share the expected result.
Update - this is what I have so far but it isn't working...
Is2ndVisit = if(calculate (distinctcount([TransactionCode]),
filter(all(CustomerMaster), CustomerMaster[CustomerCode]=min(CustomerMaster[CustomerCode])),
FILTER (ALL ( 'Calendar' ), 'Calendar'[Date] < MIN ( 'Calendar'[Date])))=1, 1, 0)Anyone able to assist?
