How to Use Power BI to Calculate Customer Lifetime Value (CLV)

6 min readJul 23, 2023

Customers are the lifeblood of any business. They provide the essential revenue and support necessary for sustainable growth. Businesses can make better-informed decisions by understanding the value a customer brings over their lifetime with the business . Customer Lifetime Value (CLV) is a highly valuable metric for businesses as it provides a comprehensive understanding of the long-term financial value that customers bring.

In this article I will leverage Power BI as a tool to calculate Customer Lifetime Value.

Table of Content

Overview of Customer Lifetime Value(CLV)
CLV Metrics
CLV Analysis
Interpreting the results

Prerequisite: Knowledge of Power BI and DAX

Customer Lifetime Value (CLV, CLTV)

Customer lifetime value (CLV, or CLTV) is a metric that illustrates the total revenue a business can reasonably earn over the entirety of their business relationship with the average customer.

There are two ways of looking at customer lifetime value: historic customer lifetime value (how much each existing customer has already spent with your brand) and predictive customer lifetime value (how much customers could spend with your brand).

This article focuses on historic CLV.

CLV Metrics

There are several methods that can be adopted in calculating CLV. The method I have adopted uses the following metrics highlighted below:

Average Purchase Value (APV): also known as Average Order Value(AOV) is the average amount of money spent by customers per transaction or order placed with a business within a specific period.
Active Customers: the total number of customers that are actively buying from a business. The active customers in this analysis are calculated based on the current month and the previous month. These metrics are needed to calculate the churn rate.
Churn Rate: the percentage of customers or subscribers who discontinue their relationship with a product, service, or subscription over a specific period of time.
Average Customer Lifespan (ALT) : the average length of time that a customer remains engaged or active with a business before churning or discontinuing their relationship.
Average Purchase Frequency (APF): the average number of purchases made by a customer within a specific time period.
Average Revenue Per Customer (ARPC): the average amount of revenue generated by each individual customer within a specific time period.

CLV Analysis

Dataset: I used the very popular superstore dataset which can be downloaded from this Kaggle link

fig 1: snapshot of the superstore dataset

Date or Calendar Table: Given that CLV is a time based metric, having a Calendar table is crucial for accurate analysis. I created a Calendar table in Power Query using M language. Here is an article that provides a guide on how to create a Calendar table in Power Query.

The Calendar table can also be created in Power BI using DAX. This article provides a guide to create the Calendar table using DAX.

Data Model: I created three dimension tables from the dataset, namely: Customer, Location, and Product. I created a relationship between my fact table (Superstore) and the four dimension tables (Calendar table inclusive). You can learn how to create a dimension table from a large fact table here.

Calculating the CLV metrics using DAX

In Power BI desktop, create new DAX measures for each of the metrics highlighted below:

Active Customers:

This calculates the distinct number of buying customers.

Active Customer =
DISTINCTCOUNT (Superstore[Customer ID])

Previous Month Active Customers:

This formula utilizes the Active Customer measure and applies a filter to select only those customers who made a purchase from the business in the previous month within the specified period.

PreviousMonthActiveCustomer =
CALCULATE ( [Active Customer], PREVIOUSMONTH ( 'Calendar'[Date] ) )

Total Sales

TotalSales = SUM(Superstore[Sales])

Total Orders

This is calculated by counting the number of distinct orders that have been completed by the business.

TotalOrders = DISTINCTCOUNT(Superstore[Order ID])

Churn Rate

To calculate the churn rate, we utilize two previously created measures: PreviousMonthCustomer and Active Customer. These two measures are stored in variables named var_BeginningCust and var_EndingCust, respectively. Another variable, var_DiffCust, is created in the formula to store the difference between var_BeginningCust and var_EndingCust. The churn rate is calculated by dividing var_DiffCust by var_BeginningCust. The result of this measure is stored as a percentage data type.

Churn Rate =
VAR var_BeginningCust = [PreviousMonthActiveCustomer]
VAR var_EndingCust = [Active Customer]
VAR var_DiffCust = BeginningCust - EndingCust

RETURN
    DIVIDE (var_DiffCust, var_BeginningCust, 0)

Average Customer Lifespan(ACL)

1/Churn rate

AverageCustomerLifespan = Divide(1,[Churn Rate],0)

Average Purchase Value(APV)

This is a division of the TotalSales measure created by the TotalOrders measure.

AveragePurchaseValue = DIVIDE([TotalSales],[TotalOrders],0)

Average Purchase Frequency(APF)

This is a division of the TotalOrders measure created by the TotalCustomers measure.

AveragePurchaseFrequency = DIVIDE([TotalOrders],[TotalCustomers],0)

Average Revenue Per Customer(ARPV)

This metric is derived by dividing the TotalSales measure by the TotalCustomers measure.

AverageRevenuePerCustomer = DIVIDE([TotalSales],[TotalCustomers],0)

Customer Lifetime Value(CLV)

The formula I used to calculate the Customer Lifetime Value (CLV) in my analysis is specific to the dataset I worked with. It does not explicitly incorporate the profit margin. However, it is worth noting that other CLV formulas commonly factor in the profit margin to provide a more accurate estimation of the long-term value a customer brings to a business.

CLV =

Average Revenue Per Customer (ARPV) x Average Customer Lifespan (ACL)

CLV = [AverageRevenuePerCustomer] * [AverageCustomerLifespan]

Viola! We have successfully calculated our Customer Lifetime Value. You can now visualize your findings.

I created a one-pager dashboard of my findings shown below:

Interpreting the result

After calculating your Customer Lifetime Value (CLV), it is important to effectively utilize the results. Interpreting CLV involves analyzing its value in relation to the business goals. Here are some key considerations when evaluating the quality of CLV:

Comparison: Compare CLV values across customer segments, product lines, or time periods. Calculating CLV across different segments provides more accurate insights as each segment may exhibit varying CLV values, enabling targeted strategies for maximizing customer value.

This comparison reveals which segments or products drive the highest lifetime value, allowing the business to prioritize marketing and retention efforts accordingly.

Trend Analysis: Examine CLV trends over time to identify its increase, decrease, or stability.

Benchmarking: Compare the CLV results with industry benchmarks or competitor analysis to assess performance in acquiring and retaining valuable customers.

Segmentation: Analyze CLV results by customer segments to reveal insights about the most valuable customer segments.

Return on Investment (ROI) Analysis: Calculate the ROI by combining CLV results with customer acquisition cost (CAC) data. This analysis will show the profitability of acquiring and retaining customers, guiding resource allocation decisions and marketing investment optimization.

Customer lifetime value (CLV) is a metric that is valuable for understanding the financial value of customers. However, interpreting CLV requires a comprehensive analysis that aligns with your organization’s goals and strategies. CLV helps businesses to prioritize resources, identify high-value customer segments, and make data-driven decisions.