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.

[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/