DAX formula for customer life time

[Anonymous]

Hello,

I have an database as shown below. I am looking for a DAX formula which looks if a client is active in a certain year and is still a client in the next year. So if I choose a particular year I can see which clients have churned. 

I hope someone can help me with this one 🙂

Greetz Tiemen

 

[v-haibl-msft · Accepted solution]

@Anonymous

I create a column with following formula. When you select a year in the slicer, the table will show the active customers in this year and show if this customer will churn in next year.

If it is not you wanted, please provide an expected output.

ChurnNextYear = 
IF (
    CONTAINS (
        Table1,
        Table1[Year as active client], Table1[Year as active client] + 1
    ),
    IF (
        ISBLANK (
            LOOKUPVALUE (
                Table1[Customer ID],
                Table1[Year as active client], Table1[Year as active client] + 1,
                Table1[Customer ID], Table1[Customer ID]
            )
        ),
        "Yes",
        "No"
    ),
    "Next year not exists"
)

Best Regards,

Herbert

[MAAbdullah_47]

hi @v-haibl-msft  @CahabaData @ankitpatira 

Could you tell me how you calculated the column [Year as active client]? I meant if it is not available at the customer table how can we calculate it based on the purchases on Sales table?

[MAAbdullah_47]

Hi, What about if the column (Year as an Active client) is not available in the customer table but you have other information such as complete name, Address,....etc. On the other hand, the sales order table has a complete history of the customer purchases through the (customer Id) as a foreign key for a certain period of time, we need your help to show it through the sales table instead of the customer table, how can we archive this through DAX? 

[Anonymous]

Do you have an example of the data? Maybe i can help

[Anonymous]

@v-haibl-msft Thanks! this one does the trick 🙂 

[CahabaData]

well I might set up 2 calculated measure fields/columns:

Start Year = MIN of client Year value

Last Active Year = MAX of client Year value

that will result in a table visual where you can see each client, Start, Last Active

.... if you want to look by year as to what clients are active - then that is achieved with your current table and a slicer by year

..... or maybe you want to do a COUNT by year just to see how the annual totals change.... 

[ankitpatira]

@Anonymous if your end goal is to just analyse which customer has continued over years then i think easiest solution will be to use standard matrix visual and have years as rows and customer ids as columns with custid also as values.