Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
@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
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:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |