Churn Rate KPI and Defining Client Type using DAX

[nick_zico]

I have had a read through the Community and, although I come across "Churn" a few times, I don't think it quite deals with the issues I have.

Issue 1:

In order to calculate the required KPI (and also other associated metrics) I have to first define clients on an annual basis as either ‘New’, ‘Repeat’, or ‘Lost’. However, this is not quite as simple as it sounds as a previously, 'Lost' client which returns should be defined as 'Repeat' (not 'New'). So, an 'historic client list amalgamation' needs to be referred to in order to attach the correct tag... I'm sorry if I haven't explained this very well but hopefully the example below will help clarify what I mean*.

Issue 2:

The next step is to create a DAX measure which can calculate the formal ‘Churn Rate’ KPI itself: “the % of existing clients which have not been retained from one reporting period to the next”.

For example, using dummy data:

Year 1                   Year 2                                    Year 3                                   Year 4

Client A                Client A (Repeat)                    Client B (Repeat)*                Client A (Repeat)*

Client B                 Client C (Repeat)                   Client C (Repeat)                    Client D (Repeat)

Client C                 Client D (Repeat)                   Client D (Repeat)                   Client E (Repeat)

Client D                Client E (New)                         Client E (Repeat)                   Client F (Repeat)

                                                                            Client F (New)                       Client G (New)

*the tag for these clients is 'Repeat', not 'New'. So, when comparing year 2-3 or 3-4 reference must be paid to previous years in order to establish that Clients A and B are 'Repeat' clients (not 'New' ones).

Manual Churn Rate Calculation:

Using the dummy table above, for Years 1 to 2:

Number of ‘Lost’ Clients in Year 2 / Number of Clients in Base Year (Year 1)

1 / 4 = 25% (as only Client B was ‘Lost’)

Years 2-3:

1 / 4 = 25% (as only Client A was ‘Lost’)

Years 3-4:

2 / 5 = 40% (two of the existing five clients at Year 3 were ‘Lost’ in Year 4: Client B and Client C)

I would be very grateful for any advice on how to overcome this. The data I am dealing with is for many hundreds of clients so having to do manual workings outside of PowerBI isn’t really an option for me; I have relied on Conditional Formatting and manual intervention in the past, but the dataset is getting bigger and bigger and it seems a sensible time to make use of more sophisticated calculations and measures.

[v-chuncz-msft]

@nick_zico,

You may use DAX below to add calculated column and measure.

Column =
IF (
    RANKX (
        FILTER ( Table1, Table1[Client] = EARLIER ( Table1[Client] ) ),
        Table1[Year],
        ,
        ASC,
        DENSE
    )
        = 1,
    "New",
    "Repeat"
)

Measure =
VAR y =
    SELECTEDVALUE ( Table1[Year] )
VAR t =
    CALCULATETABLE ( VALUES ( Table1[Client] ), Table1[Year] = y - 1 )
RETURN
    DIVIDE (
        COUNTROWS ( EXCEPT ( t, VALUES ( Table1[Client] ) ) ),
        COUNTROWS ( t )
    )

[MAAbdullah_47]

There is something missing where are the Sales (Order) table position in this calculation? 

e.g: We add a new customer to the customers' table but what if he didn't purchase any product before?

So I think we need to consider the purchases of the products not only the customer when he joined the community.

[nick_zico]

Apologies for the delay in writing back. The accounting software that houses our fee information will now be able to populate PowerBI automatically. This means it will be in a format different to the table style I mentioned above and, as such, isn't as helpful for Calculated Column purposes - or at least my understanding of them...

A table will be input in this format:

Date      Year   Client Name   Sales Dept.   Client Location   Sales Type

6/2/18   2018  Client A          Thailand       UK                     Product A

This table will house tens of thousands of individual sales records over the course of about 10 years (it is at annual level that I will need to distingish between New and Repeat clients), and will use this to compare and track a "Churn Rate" KPI, and also track weightings using visualisations.

However, the report I am designing will need to be flexible and pivot in a way where I am able to provide New/Repeat/Churn Rate analysis on a year-by-year basis at macro level (i.e. organisation as a whole), and also at micro-level, piviting the data by Client Location, Sales Dept etc.. It is this latter part that did not work on your original solution to which I am replying.

I appreciate my initial description of what I needed was not helpful; my apologies for this.

Please let me know if there is any way of using Measures instead of CC to resolve this.

Again, thanks very much in advance for your help.

Rgds,

Nick.

[nick_zico]

Your solution does work. However, the data that I will be receiving from our accounting system will now be received in a different form, and will require quite a lot of versitility and pivoting by location. As such, the static form of the calculated column "new/repeat" solution will not work. This is my fault as my initial query on this thread has now changed. I hope it won't be too much trouble for me to update this thread with the revised version of the query I have... my apologies!

I will send this over next week. Many thanks again,

Rgds,

N.

[nick_zico]

Thanks so much for the quick response. I will give this a go as soon as I find time.

Rgds,

N.