Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Date | Order ID | Customer ID | Product |
01/01/2022 | AAA | 111 | x |
01/01/2022 | AAA | 111 | y |
03/01/2022 | BBB | 111 | x |
04/01/2022 | CCC | 222 | y |
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
Solved! Go to 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.
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
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 :
The main measure is:
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:
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.
Date | Order ID | Customer ID | Product |
01/01/2022 | AAA | 111 | x |
01/01/2022 | AAA | 111 | y |
03/01/2022 | BBB | 111 | x |
04/01/2022 | CCC | 222 | y |
My previous email provided this solution.
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.
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:
product | Total Customers Purchased | Share of Total Customers | Num. Of Orders | Customers Repurchased | Repurchase Rate |
x | 1 | 50% | 2 | 1 | 100% |
y | 2 | 100% | 2 | 0 | 0% |
Total | 2 | 100% | 3 | 1 | 50% |
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:
Order | Product | Customer | Quantity |
AAA | x | 111 | 1 |
AAA | x | 111 | 1 |
AAA | x | 111 | 1 |
AAA | x | 111 | 1 |
AAA | x | 111 | 1 |
5 |
Hi:
I see. This measure is just a COUNTROWS of the Fact Table. I'll paste the model image below.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |