Flagging a customer's second visit

[srl01]

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.

[Ashish_Mathur]

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.

[srl01]

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?

[Ashish_Mathur]

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.

[srl01]

Hi Ashish

Apologies - let me rephrase then as I do indeed want a calculated column formula, but not if it takes hours to calculate. (The model has now been running for 3 hours and is still not done). 

Is there an alternative to your suggestion above (either a calculated column, or a measure) that will be computationally efficient here? 

For example... first creating a calculated column in the CustomerMaster table (meaning one calc for each of 1mil customers, rather than one for each of 5-10mil transaction lines) that looks for the earliest transaction date of that customer after their very first transaction. Then the calculated column in the SalesRawData is just a simple lookup to the CustomerMaster table to see if the date matches.

If that concept makes sense (or any other ideas) I'll gather an extract of the appropriate data and post. Thank you!

[Ashish_Mathur]

Hi,

I think we should write a measure for this.  Share the link from where i can download your file.

[srl01]

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?