First Purchase of Customer Insight Using DAX

I have another really unique insight to show to you in Power BI. After this session, you’ll be able to identify a customer’s first purchase and use this data to boost your future sales. You may watch the full video of this tutorial at the bottom of this blog.

By analyzing a trend in terms of the first product that customers are purchasing, you can: (1) change our marketing efforts; (2) allocate appropriate resources to our advertising; and (3) utilize this knowledge to get more customers on board.

You can do this because this function allows you to determine which products are more popular in the initial purchase than others. Following from this, you can work out what the second and third most popular purchases are. Eventually, you’ll see the common denominator in those particular results as well.

Table of Contents

Sample Insight Showing First Purchase History

Using the data generated from your past sales, you can easily track down the first purchase of a customer. Order history like this can help you make business decisions on how you’ll boost sales. You can also use this analysis to target your marketing and advertising for a specific product.

Below is some sample data that I’ve prepared for this tutorial. It shows the specific customer names as well as the corresponding products that they have first purchased.

I’ve set this sample data with lots of different customers. For product names, I just have generic names for easy identification. Then, I’ve collected the names of customers who have obviously purchased something already.

I’ve also added a date selector so I can easily control the range of purchase dates from any period.

What I have next is an essential part of the Power BI table. I’ve also created a table to check if I have got the data right. Since I may need to audit the numbers one way or another, this is a good measure.

Finding Out The Customer’s First Purchase

The formula that I’ve used is a combination of quite a few different functions.

First, you need to look at the TOPN formula including the SUMMARIZE and CALCULATE functions.

If you’re trying to find the first of something or last of something, TOPN is the perfect solution. This function doesn’t return a value, but it returns a table. That’s the key difference with the RANKX function, as the latter only returns a value of ranking.

What I added within TOPN is a number that suggests the table that I want to retain at the end of the evaluation.

Next, I have added the SUMMARIZE function to return a virtual table with the date and every single product.

Here’s the trick that is really crucial – you need to evaluate the minimum date or the first date.

Therefore, you need to go and find the very first date, but you need to wrap it inside the CALCULATE statement. If you don’t, you’ll actually get an incorrect result.

Now, the CALCULATE function enables us to apply a filter context to the particular result of MIN (Dates[Date]).

So, the context of this calculation starts with the customer then at every single product a customer has bought, and lastly evaluating the minimum date or the very first date that the customer bought a product.

Additionally, the MAXX function returns the actual product value that will be the actual name of the particular product.

When all these formulas are combined, it produces a very powerful insight.

Reviewing The Results

After this, you need to double-check it. It’s crucial to check if the table and the formula are actually producing the correct results.

So let’s just select a particular customer. You’ll see here that I have this table which has every date, every product, and every sale for that particular customer.

Accordingly, the very first product that was bought was on 20/8/2015. You can even see the total sales for Product 94. After that, you can try some more samples to validate the results in the table.

Moreover, you can change the time frame of your data and that’s going to change the results dynamically as well.

Conclusion

In this tutorial, I have demonstrated how you can work out this first purchase data and actually incorporate it inside a table in Power BI. Doing this can make you see the product and customer trend from any period in a dynamic way.

Hopefully, you can see that this is really powerful stuff and you can do it in a relatively efficient way using Power BI. This is what makes Power BI so great!

Good luck implementing this one in your models.

Sam

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events