churn

[Anonymous]

Hi Guys,

Thanks for your continued support. 

I have encountered a problem idendifying customers who have bought our subscriptions in one year but not another.

Below is a low level example of my problem.

customer ID	Subscription ID	Year of Subscription
ABC	1123	2015
xyz	1123	2016
mnc	1123	2017
dcs	1123	2015
hjg	1123	2019

1.  I want to identify customers who have purchased a subscription in 2016 and 2017 but not 2018.
2. I want to identify customers who have purchased a subscription in 2016 and 2018 but not 2017.
3. I want to identify customers who have purchased a subscription in 2017 and 2018 but not 2019.

I have searched online and watched several YouTube videos but haven't been able to solve the problem.

I would appreciate any help any of you kind people could give me. =]

Brendan

[Anonymous · Accepted solution]

Hi @AlB,

Thats some sweet DAX code thank you for the prompt reply. Having some issues though. Anything you could do with the following

ListCustomers1 =

VAR T2017 = CALCULATETABLE ( VALUES (Renewals[Account_ID]), Renewals[Renewal Year] = 2017 ) VAR T2018 = CALCULATETABLE ( VALUES ( Renewals[Account_ID]), Renewals[Renewal Year] = 2018 ) RETURN (EXCEPT ( T2017, T2018 ))

Error

Calculation error in measure "Renewals"[ListCustomers1]: A table of multiple values was supplied where a single value was expected.

Thanks for your help =]

[AlB · Accepted solution]

Hi @Anonymous

You are trying to squeeze that code into a measure, which does not allow multiple values.

The code generates a one-column table, as I said. Use the "New Table" button under "Modelling" to create a new table and then add the code to define it. You'll see a one-column table with the list of names. 

You can then place a table visual (or matrix) in your report and place  'Acount ID' in rows.  'Acount ID' is the only column in the generated table, ListCustomers1. 

Does that help?    

[AlB]

Hi @Anonymous

How about something like this for your requirement number 1. It's a one-column table:

ListCustomers1=
VAR T2016 =
    CALCULATETABLE (
        VALUES ( Table1[Customer ID] );
        Table1[Year of Subscription] = 2016
    )
VAR T2017 =
    CALCULATETABLE (
        VALUES ( Table1[Customer ID] );
        Table1[Year of Subscription] = 2017
    )
VAR T2018 =
    CALCULATETABLE (
        VALUES ( Table1[Customer ID] );
        Table1[Year of Subscription] = 2018
    )
RETURN
    EXCEPT ( INTERSECT ( T2016; T2017 ); T2018 )

You can follow the same pattern for the others. Add the filter for the Subscription ID if you need to. 

It could be done with slicers to dynamically determine the years rather than hard-coding them but it gets trickier.

[Anonymous · Accepted solution]

Hi @AlB,

Thats some sweet DAX code thank you for the prompt reply. Having some issues though. Anything you could do with the following

ListCustomers1 =

VAR T2017 = CALCULATETABLE ( VALUES (Renewals[Account_ID]), Renewals[Renewal Year] = 2017 ) VAR T2018 = CALCULATETABLE ( VALUES ( Renewals[Account_ID]), Renewals[Renewal Year] = 2018 ) RETURN (EXCEPT ( T2017, T2018 ))

Error

Calculation error in measure "Renewals"[ListCustomers1]: A table of multiple values was supplied where a single value was expected.

Thanks for your help =]

[AlB · Accepted solution]

Hi @Anonymous

You are trying to squeeze that code into a measure, which does not allow multiple values.

The code generates a one-column table, as I said. Use the "New Table" button under "Modelling" to create a new table and then add the code to define it. You'll see a one-column table with the list of names. 

You can then place a table visual (or matrix) in your report and place  'Acount ID' in rows.  'Acount ID' is the only column in the generated table, ListCustomers1. 

Does that help?    

[Anonymous]

Hi @AlB,

I'm out of the office now, but yeah that makes sense. My colleague and I were trawling through all of the online documentation with no success. I'll put it down to it being Friday. =]

  Thank you so much for your help.

[AlB]

@Anonymous

My pleasure

[Anonymous]

It worked thank you so much

[AlB · Accepted solution]

@Anonymous

cool. glad to hear. how about some kudos then? 

[Anonymous]

Sorry mate clearlty a noob. Gave you as much kudos as I could there.

Thanks again. =]