Churn Month by Month - Urgent

[mlaura21000]

Hi! First post here, am on a bit of an urgent timeline and I don't know how to go about this.

I'm trying to figure out our provider retention. I have multiple providers that have a total amount of sales every month. 

In a month by month basis, I need to figure out how to classify them into 3 categories: New, Churned, or Existing. These are the rules to do so. If they are present in the current month but were not there the month before they're classified as new. If they are present in the current month but not the following then they are classified as having "churned".
I'm using this formula: ProviderExistsCheck = IF(ISBLANK('table'[ProviderName]), 0, 1)  to check whether they exist and it works fine.

Rule breakdown:

The expected result for each category would look like this:

I would really appreciate any help or guidance I could be given, thank you so much!

[csco90 · Accepted solution]

Hello @mlaura21000 , 

I used the following data:

Created a calculated column:

FirstDayofMonth = DATE(YEAR(Sales[Date]), MONTH(Sales[Date]), 1)

Created the Sales and Sales Prev Measures:

Sales = VAR _sales = SUM(Sales[Sales Amount])

RETURN IF(ISBLANK(_sales), 0, _sales)

-------

Sales Prev = CALCULATE(

            [Sales],

            PARALLELPERIOD(DateTable[Date], -1, MONTH))

Created the Check forsale and check for sale prev measures:

Check for sale = IF([Sales]>0, 1, 0)

Check for sales prev = IF([Sales Prev]>0, 1, 0)

Then check for change measure:

Check for change = VAR _this_month = [Check for sale]

                    VAR _prev_month = IF([Sales Prev]>0, 1, 0)

RETURN _this_month - _prev_month

From here is easier to get when a provider churned or is new depending on the sign:

Churn = IF([Check for change] <0, 1, 0)

New = IF([Check for change]>0, 1, 0)

Please mark this as the solution if you find it correct!

[Ritaf1983]

Hi @mlaura21000 

please refer to the linked tutorial:
https://www.youtube.com/watch?v=yrdMsES-mXg

 If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly