Forum Discussion
90 Days From First Purchase Date?
Hello, I am having a difficult time figuring out this Power BI exercise that uses AdventureWorks data. Here is my PBIX file: LINK . I must replicate the following screenshot in Power BI, specifically the 90 days calculation:
I have the correct amount of customers who have made first purchases during each month, but I am not getting the correct number of customers that made a second purchase within 90 days of their original purchase to get that percentage. Am I missing something obvious, or does the data I have been provided just not match with whatever data was used to get those values in the screenshot?
Here is the DAX measure I am attempting to use to achieve this:
90 Days Purchases =
CALCULATE(
DISTINCTCOUNT(Sales[CustomerKey]),
FILTER(Sales,Sales[CustomerKey] = RELATED(Customers[AltCustomerKey])
&& Sales[OrderDate] > RELATED(Customers[DateFirstPurchase])
&& Sales[OrderDate] <= RELATED(Customers[DateFirstPurchase]) + 90
)
)
ooutput
returned users within 90 days= var ds = distinct( SELECTCOLUMNS( FILTER(Sales,Sales[CustomerKey] = RELATED(Customers[AltCustomerKey]) && Sales[OrderDate] = RELATED(Customers[DateFirstPurchase]) ), Sales[CustomerKey] )) var ds_90_days = DISTINCT( SELECTCOLUMNS( FILTER( all(sales), Sales[OrderDate]> MIN('Date'[Date]) && Sales[OrderDate] <=MIN('Date'[Date]) + 90 ),Sales[CustomerKey] )) var res = INTERSECT(ds,ds_90_days) return COUNTROWS(res)let me know if this works for you .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
8 Replies
ooutput
returned users within 90 days= var ds = distinct( SELECTCOLUMNS( FILTER(Sales,Sales[CustomerKey] = RELATED(Customers[AltCustomerKey]) && Sales[OrderDate] = RELATED(Customers[DateFirstPurchase]) ), Sales[CustomerKey] )) var ds_90_days = DISTINCT( SELECTCOLUMNS( FILTER( all(sales), Sales[OrderDate]> MIN('Date'[Date]) && Sales[OrderDate] <=MIN('Date'[Date]) + 90 ),Sales[CustomerKey] )) var res = INTERSECT(ds,ds_90_days) return COUNTROWS(res)let me know if this works for you .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠Yes! This worked for me! That is so cool, I had no idea something like INTERSECT existed, I guess you can always learn something new. Thank you very much!
If you have time, I was trying to tweak that second var block to filter for the following 3 months after a purchase (i.e. they purchase in July 2001, then did they purchase in August, September, or October 2001?). I thought DATEADD might work, but it doesn't seem to like the MINs on the 'Date'[Date] fields. Any thoughts?3 Month Users = var ds = distinct( SELECTCOLUMNS( FILTER(Sales,Sales[CustomerKey] = RELATED(Customers[AltCustomerKey]) && Sales[OrderDate] = RELATED(Customers[DateFirstPurchase]) ), 'Sales'[CustomerKey] )) var ds_3_months = DISTINCT( SELECTCOLUMNS( FILTER( all(sales), Sales[OrderDate] >= DATEADD(MIN('Date'[Date]),1,MONTH) && Sales[OrderDate] <= DATEADD(MIN('Date'[Date]),3,MONTH) ),Sales[CustomerKey] )) var res = INTERSECT(ds,ds_3_months) return COUNTROWS(res)Actually, I think I figured it out:
3 Month Users = var ds = distinct( SELECTCOLUMNS( FILTER(Sales,Sales[CustomerKey] = RELATED(Customers[AltCustomerKey]) && Sales[OrderDate] = RELATED(Customers[DateFirstPurchase]) ), 'Sales'[CustomerKey] )) var ds_3_months = DISTINCT( SELECTCOLUMNS( FILTER( all(sales), Sales[OrderDate] >= EDATE(MIN('Date'[Date]),1) && Sales[OrderDate] < EDATE(MIN('Date'[Date]),4) ),Sales[CustomerKey] )) var res = INTERSECT(ds,ds_3_months) return COUNTROWS(res)I know this has been awhile, but I have a similar request. Any idea what could be making this formula only return blank values? I've tried to change several parts but can't figure it out
The correct solution is below. I am looking for the Percent of Customers Returned with 90 Days of First Purchase and Percent Who Returned Within the Following 3 Months.
There is a Sales table that is many:one with the Customers table by CustomerKey. The Customers table has a column DateFirstPurchase. I have a calculated table Calendar one:many with the Sales table by OrderDate.
Sales table ProductKey OrderDate CustomerKey SalesOrderNumber 376 7/2/03 0:00 16688 SO51189 376 7/7/03 0:00 18212 SO51268 376 7/9/03 0:00 16702 SO51300 376 7/10/03 0:00 18246 SO51321 Customer table CustomerKey AltCustomerKey DateFirstPurchase 21602 11602 1/11/04 0:00 22517 12517 4/21/04 0:00 22518 12518 2/2/04 0:00 22714 12714 1/20/04 0:00 Calendar table Date 7/2/03 0:00 7/7/03 0:00 7/9/03 0:00 7/10/03 0:00 My intuition is that I need a COUNTROWS measure to count the rows of a table that has all CustomerKeys with a DateFirstPurchase value and the next OrderDate within 90 days of DateFirstPurchase. So if a CustomerKey has a DateFirstPurchase of 8/1/01 and the next OrderDate of 9/1/01, it would be counted. But if a CustomerKey has a DateFirstPurchase of 8/1/01 and the next OrderDate of 12/1/01, it would not be counted. For the Customers who returned within the following three months, I assume we can just expand our "window" of next OrderDate from 90 days to 180 days from DateFirstPurchase.
The below measure is what has been proposed previously on this thread, but I tried it and it is not returning the proper result. Note in my model 'Date'[Date] is changed to 'Calendar'[Date]. My 'Calendar' table is calculated by:
Calendar = CALENDAR(min(Sales[OrderDate]),max(Sales[OrderDate]))returned users within 90 days= var ds = distinct( SELECTCOLUMNS( FILTER(Sales,Sales[CustomerKey] = RELATED(Customers[AltCustomerKey]) && Sales[OrderDate] = RELATED(Customers[DateFirstPurchase]) ), Sales[CustomerKey] )) var ds_90_days = DISTINCT( SELECTCOLUMNS( FILTER( all(sales), Sales[OrderDate]> MIN('Date'[Date]) && Sales[OrderDate] <=MIN('Date'[Date]) + 90 ),Sales[CustomerKey] )) var res = INTERSECT(ds,ds_90_days) return COUNTROWS(res)
