Calculating Customer Life Time Value (CLTV) From Cohort Analysis Show Case

Dear Sam, All Members of this community

I’m very interested in calculating (CLTV) form the Cohort analysis showcase available on youtube:

I started creating the needed formulas to calculate (CLTV), If you need more information about the formulas please refer to this (2) links:
1-All Parameters: https://blog.hubspot.com/service/how-to-calculate-customer-lifetime-value
2-For calculate customer life-span: https://www.quora.com/How-do-you-calculate-customer-lifespan

I start the Dax Formulas Needed to calculate (CLTV) as the following:

A)Average Purchase Value

Average Order Size = 
AVERAGEX( Sales,
	Sales[Line Total] )

B) average purchase frequency rate
Purchase Frequency = DIVIDE([Total Transactions],[Total Customers])

C) average customer’s value
Customer Value = DIVIDE([Average Order Size],[Purchase Frequency],0)

D) average customer’s lifetime span

Please Refer to link 2 (calculate customer lifespan)

First We have to calculate the number of Churn Customers

Churn Customers = 
VAR CustomerDimension = VALUES( Customers[Customer Names] )

RETURN
IF( SELECTEDVALUE( 'Cohort Periods'[Period] ) = 0, [Unique Customers],
CALCULATE(
   CALCULATE( [Unique Customers],
        FILTER( CustomerDimension,
            COUNTROWS(
                FILTER( 'Cohort Periods',
                    [Retention Days] > 'Cohort Periods'[Min Days] && [Retention Days] <= 'Cohort Periods'[Max 
 Days] ) ) > 0 ) 
                    )
                )
      )

And

Second The Retained Customers

Retained Customers = 
    VAR UniqueCustomers = CALCULATE( [Unique Customers], ALL( 'Cohort Periods'[Period] ) )
    VAR CustomerDimension = VALUES( Customers[Customer Names] )

    RETURN
    UniqueCustomers - 
          CALCULATE( [Unique Customers],
            FILTER( CustomerDimension,
                COUNTROWS(
                    FILTER( 'Cohort Periods',
                        [Retention Days] > 0 && [Retention Days] <= 'Cohort Periods'[Max Days] ) ) > 0 ) 
                        )

Third, The churn rate calculation

Churn Rate% = 1-(DIVIDE([Retained Customers],[Churn Customers]))

Fourth, Average Customer Lifespan
Avg Customer Lifespan = DIVIDE(1,[Churn Rate%],0)

E) Finally, We calculate the customer lifetime value (CLTV)
CLTV = [Customer Value]*[Avg Customer Lifespan]

I need all members to participate in this thread to make more validation for this post and get useful experience, Thanks for Sam’s efforts and contribution to all of us, Please note all the resources available in (November 2018 Learning Summit Demo) of this website.

Thank you in advance

What is your actual specific question support question?

Where are you finding the issues in your formulas?

Maybe be more specific here.

Please also format formula correctly which is covered here

Thank You, Sam, In order to calculate the Final Deliverable (CLTV) in Paragraph (E), you have to calculate the pre-required parameters mentioned on the links at my post by doing following steps:
A. Calculate the average purchase value.
B. Calculate the average purchase frequency rate.
C. Calculate the average customer’s value.
D. Calculate the average customer’s lifetime span.

For steps (A-C, and E) all the reference formulas available on this link: https://blog.hubspot.com/service/how-to-calculate-customer-lifetime-value
For step (D) the reference formula here on this link: https://www.quora.com/How-do-you-calculate-customer-lifespan

I tried to translate(practics) all the above steps by creating DAX formula on (Cohort analysis showcase) mentioned on my previous post (Youtube & Learning Summit in 2018) as a personal effort from me based on what I learned from this community (EDNA), I post this blog to share it with you to gain new experience and correct any mistakes if occurred on my post.

For the file(resouerces) any one can download it from here:

I hope the lines above is clear enough.

Thank you

Also If you Want to check my Homework Please check here:


Please Refer to Page-1 Report of the file

The Screen-shot of the results :

I hope everything complete now , thank you for your comment.

Hi Sam

I tried my best and followed your recommendation please advise me if you have any notes.

Thank you

Still…what is your specific support question here.

What you are asking for is basically consulting by adding these links in and making someone work it all out for you.

What exact part of a formula are you requiring help on?

Please once again review the exact rules for asking questions on the forum.

Actually I didn’t say anyone work it all out for me (or on behalf of me), I take the initiative and work on it (I think this is one of your goals to improve the members skill) as Added value.I’m targeting from this post to give a new contribution to the community forum, the reference is only to give all members (Including you) Idea from where I got these formulas as a source. I liked to share it (my work) with this community as a personal effort from me (all members of this community do the same what I did) By taking advantage from what I learned, The support I’m looking for:
everyone including you can take an overview on it and correct anything if it is wrong based on the reference link and what I did in the homework, the Idea is to make this post as Knowledge useful for everyone because I believe it didn’t present before (Abstract Churn rate and Customer lifetime value).

Thank you for your cooperation.

Note: Level of Understanding is different from one person to another, I tried my best to keep following the rules of this website e.g.: (DAX Format I think I keep it as you recommend) and many other things you mentioned in the rules every time I post I got criticism. If you targeting to help me I’m open to your guide by showing which part I did wrong based on the website rules to learn from it (Your comment guidelines I believe is very generic and not specific).

Kindly we need from you to look at it and check if it is correct or not , if the content is correct (Dax expression exactly satisfy the requirements) please check it as (Ok) after geeting your feed-back (comment).
Thank you