Explaining with: Power BI Customer Lifetime Value Matrix

When the Week 6 2024 Challenge for Workout Wednesday showed up I knew this was going to be a lovely challenge of calculation practice.

Having previously build customer cohort analysis charts, this is another great one to add to your portfolio.

I've tried to push towards the expected result without creating additional tables, but wasn't able to achieve this in a reasonable amount of time before sticking to the result below.

-

!Spoiler Alert! I will walkthrough my approaches below, if you are yet to do this challenge I'd recommend you to stop reading from this point. !Spoiler Alert!

I've broken down the steps in a couple of DAX calculations and tables for you to reference to.

First, we'd like to determine the first order date of each customer. As the data table contains a record for each order date, item and customer name, we'd require context transition to return the minimum order date against each customer.

First Order Date by Customer = 
CALCULATE(
    MIN('Sample - Superstore 2019 4'[Order Date])
    ,ALLEXCEPT('Sample - Superstore 2019 4','Sample - Superstore 2019 4'[Customer Name]))

From this we can derive a nicer format that Contains the Quarter and Year represented in the chart.

Acquisition Quarter = 
FORMAT(
    'Sample - Superstore 2019 4'[First Order Date by Customer]
    ,"\Qq yyyy")

Once we have the first date of order for every customer at a row level we can carry out the date difference calculation to find out how many quarters an order occurred since their first order.

Quarters since Acquisition = 
DATEDIFF(
    'Sample - Superstore 2019 4'[First Order Date by Customer]
    ,'Sample - Superstore 2019 4'[Order Date]
    ,QUARTER)

-

The calculations above already provide us with a good base to do any type of customer cohort analysis. For instance, how many quarters did it take for a customer to order again? How much of our sales belong to customers from previous years? etc.

In this challenge though we'd like to know the Customer Lifetime Value (CLTV) which stands for the total sales of the customer cohort (Year and Quarter of first order) spread out over the following quarters.

In the visual for this challenge we see a running total of those sales over the Quarters since Acquisition.

Starting with Total Sales & Distinct Customer Count we use two measures:

Distinct Customer Count = 
DISTINCTCOUNT(
    'Sample - Superstore 2019 4'[Customer Name]
)

Total Sales = 
SUM(
    'Sample - Superstore 2019 4'[Sales]
)

Problem with these two measures is that the Acquisition Quarter and Quarters since Acquisition Columns would break up distinct customers as well as the sales.

To overcome this we'd want to return the total customers for that first Quarter and Year against every Quarters since Acquisition.

Distinct Customer Count per Acquisition Q = 
CALCULATE(
    DISTINCTCOUNT('Sample - Superstore 2019 4'[Customer Name])
    , ALLEXCEPT(
        'Sample - Superstore 2019 4'
        ,'Sample - Superstore 2019 4'[Acquisition Quarter]
        )
)

This allows us to calculate the CLTV against within a Quarter & Year of first purchase and the Quarters since Acquisition.

Customer Lifetime Value = 
[Total Sales] / [Distinct Customer Count per Acquisition Q]

-

The last element remaining is to get the running total of CLTV, which we want to occur after CLTV is calculated.

Running Total of CLTV = 
       CALCULATE(
            [Customer Lifetime Value]
            ,'Sample - Superstore 2019 4'[Quarters since Acquisition]<=MAX('Sample - Superstore 2019 4'[Quarters since Acquisition])
)

We can now start putting the Matrix together:

1. Acquisition Quarter on Rows
2. Quarters since Acquisition on Columns
3. Running Total of CLTV on Values

Soon I realised I ended up with some gaps where no sales were recorded for certain customer cohorts. Having ignored the suggestions and pre-made data model in this challenge, I realised I needed a scaffolded approach.

Time ran out and I settled with the gaps in the visual below and a cheeky overlay of the new customers with another matrix. It behaves as expected and the analysis can be carried out even with Slicers.

For the Colouring, I'd set a background colour for the Cell elements using a gradient with 3 values: White for 0 (to keep blank cells white), Light Green for 1 and Dark Green for the Highest Value.

As per usual feel free to reach out via social media if you have any questions.