Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

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

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.

Jakob_Stricker_0-1632944537723.png

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

2 REPLIES 2
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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:

Jakob_Stricker_1-1632985167343.png

 



Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.