Forum Discussion
Churn Counts Not Working
I'm trying to create a formula for a count of customers where the prior month revenue is >0 and current month revenue is =0. I've tried so many iterations of the same formula I'm seeing stars.
I have 6 categories customers should fall into. The formula is below and works fine.
Churns = SWITCH(TRUE(),
Orders[$ Current Month]>0 && Orders[# Prior Month]<=0, "NEW",
Orders[$ Current Month]=0 && Orders[$ Prior Month]=0, "N/A",
Orders[$ Current Month]<=0 && Orders[$ Prior Month]>0, "CANCEL",
Orders[$ Current Month]=Orders[$ Prior Month], "NO CHANGE",
Orders[$ Current Month]>0 && Orders[$ Prior Month]<Orders[$ Current Month], "UPGRADE",
Orders[$ Current Month]>0 && Orders[$ Prior Month]>Orders[$ Current Month], "DOWNGRADE","")
The field for customers is 'Orders'[Customer_Account]
The fields for Current and Prior Month Revenue are measures.
| 10000002 | Feb-24 | $2,695 | ($2,695) | CANCEL | |
| 10000003 | Feb-24 | $1,898 | $4,660 | ($2,762) | DOWNGRADE |
| 10000004 | Feb-24 | $3,884 | $3,884 | $0 | NO CHANGE |
| 10000005 | Feb-24 | $2,703 | $2,703 | $0 | NO CHANGE |
| 10000007 | Feb-24 | $3,045 | $3,045 | $0 | NO CHANGE |
| 10000008 | Feb-24 | $2,027 | $2,027 | $0 | NO CHANGE |
| 10000009 | Feb-24 | $991 | $991 | $0 | NO CHANGE |
| 10000011 | Feb-24 | $1,697 | $1,697 | $0 | NO CHANGE |
| 10000012 | Feb-24 | $1,500 | $1,500 | $0 | NO CHANGE |
| 10000015 | Feb-24 | $1,149 | $1,149 | $0 | NO CHANGE |
| 10000016 | Feb-24 | $2,167 | $2,167 | $0 | NO CHANGE |
Any suggestions would be great! I've checked with Google Gemini and MS CoPilot and nothing has worked.
5 Replies
Hi cherimjewell67 ,
I’d like to acknowledge the valuable input provided by lbendlin . His initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.
In my investigation, I took the following steps:
I create a table as you mentioned.
I think you can use IF function.
Status = IF ( 'Orders'[Differences] = 0, "NO CHANGE", IF ( 'Orders'[Current] = 0, "CANCEL", "DOWNGRADE" ) )Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I appreciate these responses, however, I'm trying to count the results of each status by month. Here's what the statuses look like:
Image A
Here's what I need to create.
Image B
Thank you for all your help!
Hi cherimjewell67 ,
It looks like there are a lot of unknowns in your .pbix file, could you please upload your .pbix file or more key information for me to research deeper?
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello! I'm sorry it has taken so long to reply. I'm pasting a table from an Excel workbook that has my table. I need to assign each Churn value a 1 in its own column so they can be totaled (i.e., a sum of all the 1s in New, 1s in Cancel, 1s in ADJ, etc.). The formula I'm using for my Churn status is below the table.
Table Field Table Field Table Field Measure Measure Measure
Year Month
Account #
Department
$ Prior Month
$ Current Month
Churn
2024-04
1
ABC
($235.00)
ADJ
2024-04
2
ABC
($695.00)
ADJ
2024-04
3
ABC
$89.00
CANCEL
2024-04
4
ABC
$150.00
($450.00)
CANCEL
2024-04
5
ABC
$150.00
$55.00
DOWNGRADE
2024-04
6
ABC
$621.67
$400.00
DOWNGRADE
2024-04
7
ABC
$0.00
NA
2024-04
8
ABC
$0.00
$0.00
NA
2024-04
9
ABC
$70.00
NEW
2024-04
10
ABC
$150.00
$150.00
NO CHANGE
2024-04
11
ABC
$150.00
$150.00
NO CHANGE
2024-04
12
ABC
$120.00
$150.00
UPGRADE
2024-04
13
ABC
$385.39
$687.37
UPGRADE
Formula:
Churn = SWITCH(TRUE(),
CALCULATE(
SUM('Orders'[Whole Dollars]),DATESMTD('Date'[Date]))>0 && CALCULATE(
SUM('Orders'[Whole Dollars]),DATEADD('Date'[Date],-1,MONTH)
)<=0, "NEW",
CALCULATE(
SUM('Orders'[Whole Dollars]),DATESMTD('Date'[Date]))=0 && CALCULATE(
SUM('Orders'[Whole Dollars]),DATEADD('Date'[Date],-1,MONTH)
)=0, "N/A",
CALCULATE(
SUM('Orders'[Whole Dollars]),DATESMTD('Date'[Date]))<=0 && CALCULATE(
SUM('Orders'[Whole Dollars]),DATEADD('Date'[Date],-1,MONTH)
)>0, "CANCEL",
CALCULATE(
SUM('Orders'[Whole Dollars]),DATESMTD('Date'[Date]))=CALCULATE(
SUM('Orders'[Whole Dollars]),DATEADD('Date'[Date],-1,MONTH)
), "NO CHANGE",
CALCULATE(
SUM('Orders'[Whole Dollars]),DATESMTD('Date'[Date]))>0 && CALCULATE(
SUM('Orders'[Whole Dollars]),DATEADD('Date'[Date],-1,MONTH)
)<CALCULATE(
SUM('Orders'[Whole Dollars]),DATESMTD('Date'[Date])), "UPGRADE",
CALCULATE(
SUM('Orders'[Whole Dollars]),DATESMTD('Date'[Date]))>0 && CALCULATE(
SUM('Orders'[Whole Dollars]),DATEADD('Date'[Date],-1,MONTH)
)>CALCULATE(
SUM('Orders'[Whole Dollars]),DATESMTD('Date'[Date])), "DOWNGRADE","ADJ")
Below is what I need.
Year Month
Account #
Department
$ Prior Month
$ Current Month
Churn
New
Upgrade
Cancel
Downgrade
NA
No Change
ADJ
2024-04
1
ABC
($235.00)
ADJ
1
2024-04
2
ABC
($695.00)
ADJ
1
2024-04
3
ABC
$89.00
CANCEL
1
2024-04
4
ABC
$150.00
($450.00)
CANCEL
1
2024-04
5
ABC
$150.00
$55.00
DOWNGRADE
1
2024-04
6
ABC
$621.67
$400.00
DOWNGRADE
1
2024-04
7
ABC
$0.00
NA
1
2024-04
8
ABC
$0.00
$0.00
NA
1
2024-04
9
ABC
$70.00
NEW
1
1
2024-04
10
ABC
$150.00
$150.00
NO CHANGE
1
2024-04
11
ABC
$150.00
$150.00
NO CHANGE
2024-04
12
ABC
$120.00
$150.00
UPGRADE
1
2024-04
13
ABC
$385.39
$687.37
UPGRADE
1
1
2
2
2
2
2
2
If you need any more information, please let me know. I appreciate everyone's time.
