Forum Discussion
Churn Counts Not Working
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.