Showing Churned Customer list

[Selina]

Hello everyone,

I’m having trouble visualising a list of churned customers. I have made a measurement that calculates the churn rate of customer's. I have done this with the following 2 columns in my data model:

1. A column that shows the year of the invoice.
   'Gefactureerde omzet'[Boekjaar]
2. A column that shows the name of the customer. 
   'Gefactureerde omzet'[Klantnaam]

Example:

Boekjaar	Klantnaam
2022	Customer 1
2022	Customer 2
2023	Customer 1
2023	Customer 3
2023	Customer 1
2022	Customer 4
2022	Customer 4
2022	Customer 2

My measurement to calculate the churnrate works very well. It goes as follows:

Churnrate =

VAR CCurrentYear = MAX('Gefactureerde omzet'[Boekjaar])

VAR CPreviousYear = CCurrentYear - 1

VAR CurrentYearCustomers =

CALCULATETABLE(

    VALUES('Gefactureerde omzet'[Klantnaam]),

    'Gefactureerde omzet'[Boekjaar] = CCurrentYear

)

VAR PreviousYearCustomers =

CALCULATETABLE(

    VALUES('Gefactureerde omzet'[Klantnaam]),

    'Gefactureerde omzet'[Boekjaar] = CPreviousYear

)

VAR ChurnedCustomers = EXCEPT(PreviousYearCustomers, CurrentYearCustomers)

RETURN

(IF(

    COUNTROWS(CurrentYearCustomers) = 0,

    BLANK(),

    DIVIDE(

       COUNTROWS (DISTINCT(ChurnedCustomers)),

        COUNTROWS (DISTINCT(PreviousYearCustomers))

    ))

)

However now I would like to create a visual, preferable a table list, of the customers that are listed in the variable: "ChurnedCustomers". Unfortunately I cannot use the "VALUES" DAX because this list is made within this measurement, and not a seperate column.

Does anyone have any idea how I could make a list of the customers that are listed in the "ChurnedCustomers" variable?

What I am expecting are results like:

ChurnedCustomers

Customer 2

Customer 4

(These are the customers that had an invoice in 2022, but none in 2023, and thus have left our service.)

Thank you in advance for reading and thinking with me 🙂

[v-xinruzhu-msft]

Hi @Selina 

You can try the following code

If_exist =
VAR CCurrentYear =
    MAX ( 'Gefactureerde omzet'[Boekjaar] )
VAR CPreviousYear = CCurrentYear - 1
VAR CurrentYearCustomers =
    CALCULATETABLE (
        VALUES ( 'Gefactureerde omzet'[Klantnaam] ),
        'Gefactureerde omzet'[Boekjaar] = CCurrentYear
    )
VAR PreviousYearCustomers =
    CALCULATETABLE (
        VALUES ( 'Gefactureerde omzet'[Klantnaam] ),
        'Gefactureerde omzet'[Boekjaar] = CPreviousYear
    )
VAR ChurnedCustomers =
    EXCEPT ( PreviousYearCustomers, CurrentYearCustomers )
RETURN
    IF (
        SELECTEDVALUE ( 'Gefactureerde omzet'[Klantnaam] ) IN ChurnedCustomers,
        1,
        0
    )

Then put the measure to the table visual filter

Best Regards!

Yolo Zhu

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

[Selina]

Hello Yolo,

Thank you for your response! I think it's very smart to do it this way. However it seems

RETURN
    IF (
        SELECTEDVALUE ( 'Gefactureerde omzet'[Klantnaam] ) IN ChurnedCustomers,
        1,
        0
    )

 returns a 0 for every customer. Any idea why?

[v-xinruzhu-msft]

Hi @Selina 

Or you can try the following solution.

If_exist_counts =
VAR CCurrentYear =
    MAX ( 'Gefactureerde omzet'[Boekjaar] )
VAR CPreviousYear = CCurrentYear - 1
VAR CurrentYearCustomers =
    CALCULATETABLE (
        VALUES ( 'Gefactureerde omzet'[Klantnaam] ),
        'Gefactureerde omzet'[Boekjaar] = CCurrentYear
    )
VAR PreviousYearCustomers =
    CALCULATETABLE (
        VALUES ( 'Gefactureerde omzet'[Klantnaam] ),
        'Gefactureerde omzet'[Boekjaar] = CPreviousYear
    )
VAR ChurnedCustomers =
    EXCEPT ( PreviousYearCustomers, CurrentYearCustomers )
RETURN
    COUNTROWS ( ChurnedCustomers )

Then put the If_exist_counts measure to the visual filter, set it greater than 0

Best Regards!

Yolo Zhu

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

[Selina]

Hi @v-xinruzhu-msft ,

Thank you for your fast response. Maybe I am understanding you wrong. I made a new measurement and copied your code for If_exist_counts. Next I made a table with that lists all

Gefactureerde omzet'[Klantnaam]

, and put a filter on it with measurement "If_exist_counts" and set it to greater then 0. However, it shows none (all empty) as I would expect. Because your new measurement counts the amount of churned customers. For example, with my current data it gives the value 242. So that still doesn't tell which of those customers are churned. I feel we were on the right path with your 1st solution. However every customer's value was set to 0.