Identify customers who made purchases at a particular store in their last 5 transactions

[Jayprekk]

Hi,

I have a list of transaction at user transaction level (daily transaction). I would like to know based on the latest transaction date, I would like to know for a particular store, the users that have purchased there within their last 5 transactions.

e.g I would like to know for Store A, who are the customers who purchased there in ther last 5 transactions (atlest once in their last 5 transactions).

Sample table below:

Card_number	User ID	status	Vehicle Type	store_id	store_name	date	Transaction_time	label	Product	Volume (L)	Sale Amount
A00001	100001	ACTIVE	2W	50000001	Store A	10/9/2023	00:00:00	Card	Product A	2.99	50000
A00002	100001	ACTIVE	2W	50000001	Store A	10/10/2023	00:00:00	Cash	Product A	2.92	45000
A00003	100002	ACTIVE	2W	50000001	Store B	10/9/2023	00:00:00	Cash	Product C	1.62	25000
A00004	100002	ACTIVE	4W	50000001	Store A	10/10/2023	00:00:00	Card	Product C	23.24	380000
A00005	100001	ACTIVE	2W	50000001	Store A	10/8/2023	00:00:00	Cash	Product A	2.69	41372
A00006	100001	ACTIVE	4W	50000001	Store A	10/7/2023	00:00:00	Cash	Product B	11.16	200000
A00007	100003	ACTIVE	2W	50000001	Store C	10/10/2023	00:00:00	Cash	Product D	3.25	50000
A00008	100001	ACTIVE	2W	50000001	Store A	10/6/2023	00:00:00	Card	Product B	3.25	50000

[Jayprekk]

Hi All,

I was not able to execute based on the intial solution provided. I would like to filter by store name and to be able to view the last 5 transactions of each customer based on the data fileds below. For each customer, I would like to see the user ID, date, product, store name, volume, sale amount, vehivle type. Appreciate the support. 

Card_number	User ID	status	Vehicle Type	store_id	store_name	date	Transaction_time	label	Product	Volume (L)	Sale Amount
A00001	100001	ACTIVE	2W	50000001	Store A	10/9/2023	00:00:00	Card	Product A	2.99	50000
A00002	100001	ACTIVE	2W	50000001	Store A	10/10/2023	00:00:00	Cash	Product A	2.92	45000
A00003	100002	ACTIVE	2W	50000001	Store B	10/9/2023	00:00:00	Cash	Product C	1.62	25000
A00004	100002	ACTIVE	4W	50000001	Store A	10/10/2023	00:00:00	Card	Product C	23.24	380000
A00005	100001	ACTIVE	2W	50000001	Store A	10/8/2023	00:00:00	Cash	Product A	2.69	41372
A00006	100001	ACTIVE	4W	50000001	Store A	10/7/2023	00:00:00	Cash	Product B	11.16	200000
A00007	100003	ACTIVE	2W	50000001	Store C	10/10/2023	00:00:00	Cash	Product D	3.25	50000
A00008	100001	ACTIVE	2W	50000001	Store A	10/6/2023	00:00:00	Card	Product B	3.25	50000

[123abc · Accepted solution]

1. Import Data:

   • First, import your transaction data into Power BI. You can do this by clicking on "Home" > "Get Data" and then selecting the appropriate data source and connection method.

2. Data Transformation:

   • Once your data is loaded, you may need to perform some data transformation steps to clean and structure the data. Ensure that the "date" field is recognized as a date data type.

3. Create Calculated Columns:

   • To identify the last 5 transactions for each user, you can create a calculated column that assigns a transaction rank to each transaction within a user's history, ordered by date. You can use DAX (Data Analysis Expressions) to create this calculated column. For example:

Transaction Rank = RANKX(FILTER(ALL('YourTableName'), [User ID] = EARLIER([User ID])), [date], , ASC)

1. Filter by Store:

   • Create a report or a visualization in Power BI, and add a slicer or filter that allows you to select the specific store you are interested in (e.g., "Store A").

2. Calculate Last 5 Transactions:

   • Create a measure that calculates whether a user has made a purchase in their last 5 transactions at the selected store. You can use DAX to define this measure. For example:

Purchased in Last 5 at Store A =
VAR LastTransactionRank = MAX('YourTableName'[Transaction Rank])
VAR UserTransactionsAtStoreA = CALCULATE(COUNTROWS(FILTER(ALL('YourTableName'), [store_name] = "Store A")))
RETURN IF(LastTransactionRank >= UserTransactionsAtStoreA - 4, "Yes", "No")

1. Display the Results:

   • Create a table or visual that displays the user ID and the "Purchased in Last 5 at Store A" measure. When you filter for a specific store in your slicer or filter, this table will show you which users have made purchases at that store in their last 5 transactions.

Make sure to replace 'YourTableName' with the actual name of your table in Power BI.

Remember that DAX measures can be customized to fit your specific data model, so you may need to adjust the DAX code to match your data structure and column names.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Jayprekk]

HI, 

In regards to the below portion, it seems the code indicates that we are filtering to a specific site and this does not seem to work for me. I have added the calculated column but would like to select a specific site using the slicer to allow me to choose the site that i require. Thankd for the support.

Purchased in Last 5 at Store A =
VAR LastTransactionRank = MAX('YourTableName'[Transaction Rank])
VAR UserTransactionsAtStoreA = CALCULATE(COUNTROWS(FILTER(ALL('YourTableName'), [store_name] = "Store A")))
RETURN IF(LastTransactionRank >= UserTransactionsAtStoreA - 4, "Yes", "No")

[Jayprekk]

I was able to use part of the code to get mine working. Thank you very much.