Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
@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...
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |