cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akfir
Helper IV
Helper IV

Calculate Customers with more than one order of X product

Hello masters,
I wish to create a measure which calculates the number of customers who REPURCHASED a product = bought X product in more than 1 different orders (in a dynamic range of dates).
Please note that a repurchase occures when a product is purchased by a specific customer in more than 1 order (2/3/4.....).
it actually counts the customers who bought x product in more than 1 and above order IDs.
Below example of data and the desired measure result:

DateOrder IDCustomer IDProduct
01/01/2022AAA111x
01/01/2022AAA111y
03/01/2022BBB111x
04/01/2022CCC222y


In this range of dates - product x was repurchased by 1 customer (111) , product y was not repurchased by any customer.

thanks in advance champs,
Amit

1 ACCEPTED SOLUTION

Hi:

Yes, please connect date to current date table. Use new date table for slicer. I'llpaste Date Table code below. Mine changes when I select Jan 1-2-3 or 4.

 

Whitewater100_0-1653769178904.png

Please mark as Date Table. I called this second date table(the first one was just outdated) "Dates 2"

Dates 2=

 

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

 

VAR FiscalMonthEnd = 12

 

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

 

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

 

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

 

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

 

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

    "Day Name",FORMAT([Date],"DDDD"),

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

 

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

 

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

 

RETURN WorkingDays

View solution in original post

13 REPLIES 13
Whitewater100
Solution Sage
Solution Sage

Hello:

I happen to have a file on this topic. Please see additional measure at bottom.

https://drive.google.com/file/d/1Gu_IBivGw5eOtOUs1bnloNkD6NjmQxDo/view?usp=sharing 

 

For Calc Col :

Multi-purchase CC =
var vproduct = Data[Product]
return
CALCULATE(
COUNTROWS(Data),
Data[Product] =vproduct,
ALLEXCEPT(Data, Data[Customer ID]))
Whitewater100_0-1653685293565.png

 

The main measure is:

Customers w/Multi Purchases =
COUNTROWS(
FILTER(
SUMMARIZE( Cust_Sales, Cust_Sales[Product],
"Total Purchases", [Transactions] ),
[Total Purchases] >= 2 ) )
 
However, you should see model design to fully understand. I hope this solves your question. Thanks..

Thanks for your response.
unfortunatelly  i am unable to download your shared PBI file due to business restrictions.
however, what is your [transactions] measure?

Hi:

You want no. customers. Now you have how many purchases per item and no of customers with more than one purchase, mage below. I added this measure:

Customers w/Multiply Purchases (Data) =
var ct = SELECTEDVALUE(Data[Multi-purchase CC])
return
IF(ct > 1,CALCULATE(DISTINCTCOUNT(Data[Customer ID])))
Whitewater100_1-1653742986206.png

 

 

it didnt work unfortunately.
all data i have is one table (customers and orders). i wish to count unique customers who purchased x product (there are 8 available) in more than one order (order id)

Hi akfir:

The data you provide is below. My table has one customer (ID 111) who had repeat customers.

DateOrder IDCustomer IDProduct
01/01/2022AAA111x
01/01/2022AAA111y
03/01/2022BBB111x
04/01/2022CCC222y

 

My previous email provided this solution.

Whitewater100_0-1653755170000.png

It appears to match your example:

In this range of dates - product x was repurchased by 1 customer (111) 

 

I'm not following as I only see 4 records with two products?

Thanks for sending your project!
after trying to implement it in my project and getting constant zeros for "Multi Purch Customer CT" for each product, i figured out that in your project - and in mine as well - this measure does not change according to date slicer.

can you please check this out?

Hi:

Yes, please connect date to current date table. Use new date table for slicer. I'llpaste Date Table code below. Mine changes when I select Jan 1-2-3 or 4.

 

Whitewater100_0-1653769178904.png

Please mark as Date Table. I called this second date table(the first one was just outdated) "Dates 2"

Dates 2=

 

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

 

VAR FiscalMonthEnd = 12

 

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

 

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

 

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

 

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

 

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

    "Day Name",FORMAT([Date],"DDDD"),

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

 

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

 

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

 

RETURN WorkingDays

Thanks! perfect!

Excellent. Glad it worked out!

can you send your project privately by email? (amitkfir7733@gmail.com)
my aim is showing this table and measures:

productTotal Customers PurchasedShare of Total CustomersNum. Of OrdersCustomers RepurchasedRepurchase Rate
x150%21100%
y2100%200%
Total2100%3150%

Yes, I will now. Please look at page "UG". The first two pages might be interesting to you as well. Thank you.

Please mark as solution when you verify. 

please note that x orderid can contain several rows for same product, so COUNTROWS is not usefull in this case:

OrderProductCustomerQuantity
AAAx1111
AAAx1111
AAAx1111
AAAx1111
AAAx1111
   5

Hi:

I see. This measure is just a COUNTROWS of the Fact Table. I'll paste the model image below.

 

Transactions = COUNTROWS(Cust_Sales)
 
You can also just add a measure if you are working in one table:
Multi Purchase Final =
var multi = SELECTEDVALUE(Data[Multi-purchase CC])
return
IF(multi>1, MAX(Data[Multi-purchase CC]))
Whitewater100_0-1653741356689.png

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors