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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akfir
Helper V
Helper V

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors