DAX- New customer average spend (Cohort Analysis)

[Vsthesound]

Hi @tamerj1 & @Greg_Deckler  ,

I am trying to acheive a cohort of the average spend of new customers. Please see screenshot below of what I want to acheive.

However, I cant seem to get the DAX right. Can anyone please help?, i cant seem to get it right?.

This is my DAX so far.

New customer avg spend =

                    VAR FirstOrderMonth = SELECTEDVALUE(Sales[First Order Month])

                    VAR CurrentMonthAfter = SELECTEDVALUE('Future Months Table'[Value])

                    VAR CurrentCustomers= VALUES(customer[Customer ID])

                    VAR PastCustomers= CALCULATETABLE(

                                                VALUES(customer[Customer ID]),

                                                ALL(Sales[Order Date]),

                                                EOMONTH(MIN(Sales[Order Date]),0)<FirstOrderMonth)

                    VAR NewCustomers= EXCEPT(CurrentCustomers,PastCustomers)

                    VAR NewCustFutureMonths = CALCULATETABLE(VALUES(customer[Customer ID]),

                     ALL(Sales[Order Date]),

                     Sales[Order Date]=FirstOrderMonth+CurrentMonthAfter)

                     VAR NewCustInFutureMonths = INTERSECT(NewCustomers,NewCustFutureMonths)

                    RETURN

                    CALCULATE(

                              DIVIDE(

                              SUM(Sales[Sales]),

                              COUNTROWS(NewCustInFutureMonths)),

                              ALL(Sales[Order Date]),

                              EOMONTH(MIN(Sales[Order Date]),0)=FirstOrderMonth+CurrentMonthAfter)

Thank you.

[Vsthesound]

@tamerj1 , this is what the sales table looks like.

This is what the sales table looks like.

[Vsthesound]

Thank you for your swift response @tamerj1 

On the columns of the cohort..we have the Sales[First Order Month] : this is the month where the customer first made a purchase. The DAX i used was EOM(MIN('Sales'[Order Date]),0)

On the rows of the matrix we have Month 0, Month 1 etc which are values of a column in the  "Future Months Table"

Sales[First Order Month] column is not in the customer table because it was derived as a calculated column from order dates, which is also a column in the sales table.

Please note that this dataset had only two tables, Sales and Customer table. Everything related to the order ID,Order Date was contained in the sales table.

I hope I was able to answer your questions. If you have further questions , please let me know.

Thank you.

[tamerj1]

@Vsthesound 
But shouldn't a customer have multiple orders? In this case he will also have multiple first order months! While logicaly this hould be a single value for each customer.  Am I right? Would you please clarify this point. Thank you for your patience. 

[Vsthesound]

@tamerj1 

Yeah, they do have multiple orders. Thats why to get the First order date, i simply used "MIN" function to get the earliest date of purchase , even though they may have multiple purchases/orders.

I have attached a link to the pbix file for further understanding of the data.

https://drive.google.com/file/d/1Z5EmIJ9oWBMVOFnnRU7mz5ztoFEewKIo/view?usp=share_link

[tamerj1 · Accepted solution]

Hi @Vsthesound 

I'm not expert in cohort analysis, therefore, I tried to understand the logic of your analysis by tracking the logic of your DAX.
My understanding is that you want to track the sales of the new customers in a certain month starting from the very same month up to 12 months forward.
Let's say customers X, Y & Z are the new customers in Feb. 2016 so we need to calculate the amount spent by these particular 3 customers for each of the 13 months starting from Feb 2016 and ending with Feb. 2017

This monthly amount shall be divided by the number of the new customers on the relevant month.

Please let me know if my understanding is correct or not and I would really love to hear your complete explanation of this type of analysis and meaning/reason of this ratio/average calculation.

Here is my proposed solution based on my understanding of the problem and the requirements.

New customer avg spend = 
VAR FirstOrderMonth =
    SELECTEDVALUE ( Sales[First Order Month] )
VAR CurrentMonthAfter =
    SELECTEDVALUE ( 'Future Months Table'[Value] )
VAR CurrentNewCustomers = VALUES ( Sales[Customer ID] )
VAR FutureNewCustomers =
    CALCULATETABLE (
        VALUES ( Sales[Customer ID] ),
        ALL ( Sales ),
        EOMONTH ( Sales[First Order Month], - CurrentMonthAfter ) = FirstOrderMonth
    )
RETURN 
    CALCULATE (
        DIVIDE ( SUM ( Sales[Sales] ), COUNTROWS ( FutureNewCustomers ) ),
        ALL ( Sales ),
        EOMONTH ( Sales[Order Date], - CurrentMonthAfter ) = FirstOrderMonth,
        CurrentNewCustomers
    )

[Vsthesound]

@tamerj1 , this is very correct. Thank you very much.

[tamerj1]

Hi @Vsthesound 

which columns are on rows and on the columns of the matrix? And from which tables? Please elaborate on the Sales[First Order Month] column and why it is in the Sales table not in the customer table? Is it related to customer or product? Please expect further questions after your answer.