Calculate Proportion of Loyal/Churn/New Customers

[lwklwk98]

Hello PowerBI Community!

I really need your help. I am tasked to derive insights in an extensive sales dataset that spans across 3 years; last year, this year, and next year. 

Here is a mock-up dataset:

There are 3 different Customer Types and Qty shown for 2022 are expected Qty. The months are running from Jan 2020 to Dec 2022. Even when the customer has churned, the remaining period will still reflect 0 as sales. 

Customer No.	Customer Type	Jan 2020 Sales	Feb 2020 Sales...	Jan 2021 Sales	Feb 2021 Sales...	Jan 2022 Sales	Feb 2022 Sales...
1	A	900	200	0	0	0	0
2	B	300	200	0	0	100	500
3	A	0	0	0	0	500	200
4	C	0	0	200	300	150	400

I am looking to create a few graphs to show the figures/proportion of Potential New Customers beyond Jun 2021 (Group 1) and Customers that Churned (Group 2) and Loyal Customers (Group 3).

In this example, Customer No. 1 will belong to Group 2. Customer No. 2 and 4 will belong to Group 3.  Customer No. 3 will belong to Group 2.  

Really appreciate any help! Thank you very much.

[Icey · Accepted solution]

Hi @lwklwk98 ,

Please check if this is what you want:

Group Column = 
VAR FirstDayofCurrentMonth =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR Sales_CurrentMonth =
    CALCULATE (
        SUM ( Sheet1[Sales] ),
        FILTER (
            Sheet1,
            Sheet1[Customer No.] = EARLIER ( Sheet1[Customer No.] )
                && Sheet1[MonthYear] = FirstDayofCurrentMonth
        )
    )
VAR Sales_beyond =
    CALCULATE (
        SUM ( Sheet1[Sales] ),
        FILTER (
            Sheet1,
            Sheet1[Customer No.] = EARLIER ( Sheet1[Customer No.] )
                && Sheet1[MonthYear] > FirstDayofCurrentMonth
        )
    )
VAR Sales_previous =
    CALCULATE (
        SUM ( Sheet1[Sales] ),
        FILTER (
            Sheet1,
            Sheet1[Customer No.] = EARLIER ( Sheet1[Customer No.] )
                && Sheet1[MonthYear] < FirstDayofCurrentMonth
        )
    )
RETURN
    SWITCH (
        TRUE (),
        Sales_previous = 0
            && Sales_CurrentMonth = 0
            && Sales_beyond > 0, "Potential New Customers",
        Sales_beyond = 0
            && ( Sales_previous <> 0
            || Sales_CurrentMonth <> 0 ), "Customers that Churned",
        "Loyal Customers"
    )

Group Measure = 
VAR FirstDayofCurrentMonth =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR Sales_CurrentMonth =
    CALCULATE ( SUM ( Sheet1[Sales] ), Sheet1[MonthYear] = FirstDayofCurrentMonth )
VAR Sales_beyond =
    CALCULATE ( SUM ( Sheet1[Sales] ), Sheet1[MonthYear] > FirstDayofCurrentMonth )
VAR Sales_previous =
    CALCULATE ( SUM ( Sheet1[Sales] ), Sheet1[MonthYear] < FirstDayofCurrentMonth )
RETURN
    SWITCH (
        TRUE (),
        Sales_previous = 0
            && Sales_CurrentMonth = 0
            && Sales_beyond > 0, "Potential New Customers",
        Sales_beyond = 0
            && ( Sales_previous <> 0
            || Sales_CurrentMonth <> 0 ), "Customers that Churned",
        "Loyal Customers"
    )

Note: I convert "MonthYear" to Date type and it use the first day of each month by default, no other dates. Therefore, only first day of each month is used in DAX.

Best Regards,

Icey

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

[amitchandak]

@lwklwk98 , I am not very clear on the logic

Potential New Customers beyond Jun 2021 (Group 1) and Customers that Churned (Group 2) and Loyal Customers (Group 3), Can you please explain.

Usually based on time duration we can get customer Retention 

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

[lwklwk98]

Hi Amit, thanks for your reply.

To elaborate on my sentence, what I mean is that I would like to categorize my customers into 3 groups as mentioned. 

The first group will be the Potential New Customers. These are customers that provide me with sales beyond the current month I am at, which in this case will be Jun 2021. 

The second group will be the Customers that churned. These are customers that do not have any sales with me beyond the current month I am at. 

The third group will be my loyal customers which are basically customers that do not fall within either of the above 2 groups. 

Hope that clarifies!