Count Customers shopped different time periods

[Anonymous]

Hi,

I'm looking to get a count of customers who have shopped in the last 13 months, and also shopped in the prior 13 months.

I have a fact table [orders], date table [dates] and customer table [customers].

Order table is linked to customer table by column [email] which is to be used for the count. Order table is also linked to dates table through [FiscalDay] column.

I can get the count of the last 13 months to work, but can't see a count of customers who shopped in BOTH periods.

Thanks

Mike

[v-shex-msft · Accepted solution]

Hi @Anonymous,

>>I can get the count of the last 13 months to work, but can't see a count of customers who shopped in BOTH periods.

Maybe you can try to following my logic:

1. Create variable to store customer list shopped in last 13 months.
2. Create variable to store customer list who shopped between last 26 months and last 13 months.
3. Use INTERSECT function to get customers who both shopped in two periods.
4. Count above intersect list.

If you have confused on coding formula, please share some sample data to help us clarify your table structure and test to coding formula.

Regards,

Xiaoxin Sheng