cancel
Showing results for
Did you mean:
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 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.

1 ACCEPTED SOLUTION
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"
)
``````

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.

3 REPLIES 3
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"
)
``````

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.

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

Proud to be a Super User!

Helper I

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!

Announcements