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.
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:
Measures:
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!
Solved! Go to 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.
@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.
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 |
---|---|
106 | |
104 | |
79 | |
68 | |
61 |
User | Count |
---|---|
144 | |
104 | |
103 | |
82 | |
70 |