Time Intelligence: Calculate order rate by month, for specific customers in succeeding 60 days

[niko18033]

Hi PPI Experts!

I have a tricky Time Intelligence problem I'm stumped on. I'd like to calculate something let's call "order rate", which is the percent of all customers from a given month who make an order within 60 days of the month they signed up (enroll date) for the service.

Below is some sample data. Here's my issue. For the given enroll month, I want the numerator (# of Customers w/ Order) to be the # of customers from the enroll month who placed an order within 60 days of the day upon which they enrolled. Currently, it's just calculating the # of Customers who placed an order in the same month in which they enrolled. Rather, I'd like to find the # of Customers who placed an order within 60 days of their enroll date, but keep the 'axis' to the month which they enrolled.

For example, let's take the month beginning 5/1/2021. Of the 717 customers who enrolled in May 2021, I want to calculate how many of those particular 717 customers placed an order at any point within 60 days of their respective enroll date.

Enroll Month	Order Rate	# of Customers w/ Order within 60 days	# of Customers
7/1/2020	33.33%	2	6
8/1/2020	18.82%	16	85
9/1/2020	24.66%	54	219
10/1/2020	17.02%	64	376
11/1/2020	13.66%	59	432
12/1/2020	9.59%	51	532
1/1/2021	14.38%	44	306
2/1/2021	9.75%	39	400
3/1/2021	9.22%	51	553
4/1/2021	5.37%	36	670
5/1/2021	4.74%	34	717
6/1/2021	2.69%	24	891
7/1/2021	0.54%	5	930
8/1/2021	0.48%	2	414

Enroll Month is simply a monthly aggregate of the 'enroll day' field (new group of 'enroll day'). The raw data fields would be:

• Enroll Day (data type: Date)
• Customer ID (data type: Text)
• Order ID (data type: Text)

Measures: 

• # of Customers = DISTINCTCOUNT(Customer ID)
• # of Customers w/ Order = CALCULATE(DISTINCTCOUNT(Customer ID), Order ID <> BLANK())

Please help me solve for the measure: # of Customers w/ Order within 60 days

Any assistance would be much appreciated! Please let me know if you need me to provide any further details. Thanks in advance!

[Greg_Deckler · Accepted solution]

@niko18033 I feel like I am missing half the information needed to solve this like, is there a separate sales table that has the orders in it with the date they were ordered? But in general it should look something along the lines of:

Measure = 
  VAR __EnrollDate = MAX('Table'[Enroll Month])
  VAR __60DaysLater = (__EnrollDate + 60) * 1.
  VAR __Table = FILTER(ALL('Sales'),[Order Date] >= __EnrollDate && [Order Date]<=__60DaysLater)
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"CustomerID",[CustomerID])))

But again, this is a lot of speculation on my part. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.