cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lwklwk98
Helper I
Helper I

Calculate Proportion of Loyal/Churn/New Customers

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 SalesFeb 2020 Sales...Jan 2021 SalesFeb 2021 Sales...Jan 2022 Sales Feb 2022 Sales...
1A9002000000
2B30020000100500
3A0000500200
4C00200300150400

 

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.

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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"
    )

 

group.PNG

 

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.

View solution in original post

3 REPLIES 3
Icey
Community Support
Community Support

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"
    )

 

group.PNG

 

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.

View solution in original post

amitchandak
Super User IV
Super User IV

@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...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors