Forum Discussion

Selina's avatar
Selina
Frequent Visitor
2 years ago

Showing Churned Customer list

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 🙂

Need Help
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Selina
      Frequent Visitor
      2 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.