Wednesday, November 18, 2026

How to Analyze Customer Purchase frequency and Identify Inactive Customers using DAX in Power BI

How to find First and Second Purchase Dates and detect Non-Recurring Customers using DAX in Power BI
Scenario:
Understanding customer purchase behavior is one of the most valuable applications of data analytics. Beyond knowing what customers purchase, businesses often want to understand how quickly customers return after their first purchase and which customers have stopped purchasing altogether.

These insights help organizations measure customer engagement, improve retention strategies, and identify opportunities to increase Customer Lifetime Value (CLV).

In this article, we will use a simple transaction dataset to address below two common business scenarios:

Business Scenario 1: Customer Repeat Purchase Analysis:
Find the First and Second Purchase Dates for each Customer and calculate the number of days between them.

Business Scenario 2: Identifying Non-Recurring Customers:
Identify Non-Recurring (Inactive) Customers who have not made a purchase in the last 90 days or 3 months.

We can explore these scenarios further using the following Power BI data model:


Relationships of the Data Model:


dim_Customer:

dim_Products:

dim_Calendar (Date Ranges for 2026):


fact_Transactions:

Business Scenario 1: Customer Repeat Purchase Analysis:
Business to understand how quickly customers return after making their first purchase. The time gap between the first and second purchases is a key indicator of customer engagement and purchasing behavior.

Customers who return quickly are often more engaged and have a higher likelihood of becoming long-term customers.

Business Benefits:
  • Measure Customer Engagement by tracking how quickly customers make a second purchase.
  • Identify High-Value Repeat Buyers who demonstrate strong purchasing behavior.
  • Improve Marketing Timing by understanding the optimal period for follow-up campaigns.
  • Support Customer Retention Strategies using customer purchase patterns.
  • Increase Customer Lifetime Value (CLV) through proactive engagement.
We can achieve this Scenario using the below DAX Query Logics:

Total Sales = SUM(fact_Transactions[Sales_Amount])

First Purchase Date =
VAR _Result =
IF ( NOT ISBLANK( [Total Sales]),
    CALCULATE (
        MIN ( dim_Calendar[Date_Key] ),
        REMOVEFILTERS ( dim_Products[Product_Name] ),
        REMOVEFILTERS ( dim_calendar[Date_Key] )
    )
)
RETURN
IF ( HASONEVALUE ( dim_Customer[Customer_Name] ), _Result)

First Purchase Product =
VAR _FirstDate = [First Purchase Date]
VAR _FirstProductID =
    CALCULATE (
        MIN ( fact_Transactions[Product_ID] ),
        REMOVEFILTERS ( dim_Products[Product_Name] ),
        REMOVEFILTERS ( dim_Calendar[Date_Key] ),
        dim_Calendar[Date_Key] = _FirstDate
    )
RETURN
    LOOKUPVALUE (
        dim_Products[Product_Name],
        dim_Products[Product_ID], _FirstProductID
    )

Second Purchase Date =
VAR _FirstDate = [First Purchase Date]
VAR _Result =
IF (
    NOT ISBLANK ( [Total Sales] ),
    CALCULATE (
        MIN ( fact_Transactions[Transact_Date] ),
        REMOVEFILTERS ( dim_Products[Product_Name] ),
        REMOVEFILTERS ( dim_Calendar[Date_Key] ),
        KEEPFILTERS ( fact_Transactions[Transact_Date] > _FirstDate )
        )
    )
RETURN
IF ( HASONEVALUE ( dim_Customer[Customer_Name] ), _Result)

Second Purchase Product =
VAR _SecondDate =  [Second Purchase Date]
VAR _SecondProductID =
    CALCULATE (
        MIN ( fact_Transactions[Product_ID] ),
        REMOVEFILTERS ( dim_Products[Product_Name] ),
        REMOVEFILTERS ( dim_Calendar[Date_Key] ),
        dim_Calendar[Date_Key] = _SecondDate
    )
RETURN
    LOOKUPVALUE (
        dim_Products[Product_Name],
        dim_Products[Product_ID], _SecondProductID
    )

The following measure returns the time gap in days between a customer’s first and second purchases to assess repeat purchase behavior.
Days Between First and Second Purchases =
IF (
    NOT ISBLANK ( [Second Purchase Date] ),
    DATEDIFF (
        [First Purchase Date],
        [Second Purchase Date],
        DAY
    )
)

To Identify Customers Who Purchased a Specific Product, Such as "Radio":
Customers Purchased Radio =
VAR _ProductsPurchased =
    CALCULATE (
        DISTINCTCOUNT ( fact_Transactions[Product_ID] ),
        REMOVEFILTERS ( dim_Products )
    )

VAR _RadioPurchased =
    CALCULATE (
        COUNTROWS ( fact_Transactions ),
        REMOVEFILTERS ( dim_Products ),
        dim_Products[Product_Name] = "Radio"
    )

RETURN
IF (
    NOT ISBLANK ( [Total Sales] )
        && _ProductsPurchased = 1
        && _RadioPurchased > 0,
    SELECTEDVALUE ( dim_Customer[Customer_Name] )
)

To Identify Customers Who Purchased Only a Specific Product, Such as "Router", and No Other Products:
Customers Only Purchased Router =
VAR _TotalSales =
    CALCULATE (
        [Total Sales],
        REMOVEFILTERS ( dim_Products ),
        REMOVEFILTERS ( dim_Calendar ),
        VALUES ( dim_Customer[Customer_ID] )
    )

VAR _RouterSales =
    CALCULATE (
        [Total Sales],
        VALUES ( dim_Customer[Customer_ID] ),
        dim_Products[Product_Name] = "Router"
    )

RETURN
IF (
    _TotalSales = _RouterSales
    && _RouterSales > 0
    && SELECTEDVALUE ( fact_Transactions[Product_ID] ) = 103, --"Router"
    MAX ( dim_Customer[Customer_Name] )
    )

# The results of all the above logics for Scenario-1 are shown below:


Business Scenario 2: Identifying Non-Recurring Customers:
Customer retention is often more cost-effective than acquiring new customers. By identifying customers who have not purchased within a defined period, business can proactively launch retention campaigns, improve engagement, and encourage repeat purchases.

In this scenario, a customer is considered Non-Recurring (Inactive) if they have not made a purchase in the Last 90 days or last 3 months.

Business Benefits:
  • Identify Inactive Customers who have stopped purchasing.
  • Launch Targeted Retention Campaigns to re-engage dormant customers.
  • Improve Customer Retention through timely outreach.
  • Optimize Marketing Spend by focusing on customers most likely to return.
  • Monitor Customer Health using purchase recency.
  • Support Data-Driven Decision-Making for customer engagement initiatives.
We can achieve this Scenario using the below DAX Query Logics:

Last Purchase Date =
VAR _Result =
    IF (
        NOT ISBLANK ( [Total Sales] ),
        CALCULATE (
            MAX ( dim_Calendar[Date_Key] ),
            REMOVEFILTERS ( dim_Products[Product_Name] ),
            REMOVEFILTERS ( dim_Calendar[Date_Key] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( dim_Customer[Customer_Name] ),
        _Result
    )

Last Purchase Product =
VAR _LastDate =  [Last Purchase Date]
VAR _LastProductID =
    CALCULATE (
        MIN ( fact_Transactions[Product_ID] ),
        REMOVEFILTERS ( dim_Products[Product_Name] ),
        REMOVEFILTERS ( dim_Calendar[Date_Key] ),
        dim_Calendar[Date_Key] = _LastDate
    )
RETURN
    LOOKUPVALUE (
        dim_Products[Product_Name],
        dim_Products[Product_ID], _LastProductID
    )

To Identify the number of Days elapsed between the Customer's most recent purchase date and the current date:

Days Inactive after Last Purchase =
VAR _LastPurchaseDate = [Last Purchase Date]
RETURN
    IF (
        NOT ISBLANK ( _LastPurchaseDate ),
        DATEDIFF (
            _LastPurchaseDate,
            TODAY (),
            DAY
        )
    )


/*
We can use below logic to classify each customer based on their purchase activity.
Non-Recurring : Last purchase was more than 90 days ago.
Active : Last purchase was within the last 90 days.
*/

Customer Purchase Status in Last 90 Days =
VAR _DaysInactive = [Days Inactive after Last Purchase]
RETURN
    IF (
        NOT ISBLANK ( _DaysInactive ),
        IF (
            _DaysInactive > 90,
            "Non-Recurring",
            "Active"
        )
    )


# The results of all the above logics for Scenario-2 are shown below:


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose we have a sample Sales data, which is stored with Monthly Snapsh...

Popular Posts from this Blog