Extract first unique customer ID in a given month (cohort analysis)

Anonymous — Wed, 29 Sep 2021 19:58:35 GMT

Hi Everyone.
I am currently working on a cohort analysis in PowerBI, where I would like to show when the different customers from a given cohort starts to buy again. I have currently setup the cohort analysis in a matrix, which is working fine, but I have trouble to calculate the time when a customer makes a purchase again. To give you a better understand of the current setup, then take a look at the snippet below.

As you can see is the total of the number of customer retention being calculated correctly, but if we aggregate the data on to dates (in this scenario primo, medio, ultimo) on the given month, then it counts an unique customer id multiple time.
What I want to achive is, when an unique customer ID has appeared once in the given month, then it should only count the first time it appears (eg. only count when a customer made first purchase the 2th and not the 10th).
My current DAX for the "Customer Retention num" looks like this:

Customer Retention num = VAR CurrentMonthAfter = SELECTEDVALUE('Months After'[Value]) # Value is a generated serie (GENERATESERIES(0,12,1)) which specifies the months after the observed month VAR CurrentFirstOrderMonth = SELECTEDVALUE(CustomerID_Table[First Order Date (EDM)]) # First order date is a coloum given by the following dax # VAR CurrentCustomer = CustomerID_Table[customerID] # RETURN # CALCULATE( # EOMONTH(MIN(CustomerID_Table[date]),0), # FILTER( # CustomerID_Table, # CustomerID_Table[customerID] = CurrentCustomer # ) #) RETURN CALCULATE( DISTINCTCOUNT(CustomerID_Table[customerID]), FILTER( CustomerID_Table, EOMONTH(CustomerID_Table[date],0) = EOMONTH(CurrentFirstOrderMonth, CurrentMonthAfter) ) )

Hope my case makes sense.

Best Regards
Jakob

Re: Extract first unique customer ID in a given month (cohort analysis)

amitchandak — Thu, 30 Sep 2021 04:42:56 GMT

Anonymous , First sales is first sales in the month or overall ?

I have done something similar in the blog

Customer Retention Part 3: Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Analysis/ba-p/1393410

Re: Extract first unique customer ID in a given month (cohort analysis)

Anonymous — Thu, 30 Sep 2021 06:59:47 GMT

Hi amitchandak
In my scenario I would like to track down a customer's first purchase in a month and avoid any duplicates if the customer has multiple purchases in one day. The reason for this is that I would like to create a visualization like the one seen in the tooltip below, but just with the right numbers:

topic Re: Extract first unique customer ID in a given month (cohort analysis) in DAX Commands and Tips

Extract first unique customer ID in a given month (cohort analysis)

Re: Extract first unique customer ID in a given month (cohort analysis)

Re: Extract first unique customer ID in a given month (cohort analysis)