Churn Counts Not Working

[cherimjewell67]

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.

$ Current Month =

CALCULATE(

    SUM('Orders'[Revenue]),DATEADD('Date'[Date],1,MONTH)

    )

AND

$ Prior Month =

CALCULATE(

    SUM('Orders'[Revenue]),DATEADD('Date'[Date],-1,MONTH)

    )

The equation I'm using is:

Cancel =

CALCULATE(

  DISTINCTCOUNT(Orders[Customer_Account]),

  'Orders'[$ Prior Month] > 0),

  'Orders'[$ Current Month] <= 0))

I get an error:

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed. 

I've tried another iteration adding FILTER, I still get an error. 

Here's a small snippet of the data I'm working with.

Customer_AccountYear Month$ Current Month$ Prior Month$ DifferenceChurn Status

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.

[cherimjewell67]

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.       

[v-yilong-msft]

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.

[cherimjewell67]

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!

[v-yilong-msft]

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.

[lbendlin]

This may help: https://www.daxpatterns.com/new-and-returning-customers/