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 There,
I am trying to calculate the time between a customer's first order and second order. I have been able to calculate the difference between first and last date as follows using measures:
Date Of First Purchase = FIRSTDATE('Sales Fact Table'[purchase_date])
Date Of Last Purchase = LASTDATE('Sales Fact Table'[purchase_date])
Days Between First And Last = DATEDIFF([Date Of First Purchase], [Date Of Last Purchase], day )
However I want to find the difference between first order and second order, then between second order and third order and so on.
Anyone know how I would go about this?
Thanks
Solved! Go to Solution.
You can try this MEASURE pattern
Days between second order and third order = VAR temp = ADDCOLUMNS ( VALUES ( 'Sales Fact Table'[purchase_Date] ), "RANK", RANKX ( VALUES ( 'Sales Fact Table'[purchase_Date] ), [purchase_Date], , DESC, DENSE ) ) RETURN DATEDIFF ( MINX ( FILTER ( temp, [RANK] = 3 ), [purchase_Date] ), MINX ( FILTER ( temp, [RANK] = 2 ), [purchase_Date] ), DAY )
Try this MEASURE to do the average
Measure = AVERAGEX ( ALLSELECTED ( 'Sales Fact Table'[Customer] ), [Days between second order and third order] )
You can try this MEASURE pattern
Days between second order and third order = VAR temp = ADDCOLUMNS ( VALUES ( 'Sales Fact Table'[purchase_Date] ), "RANK", RANKX ( VALUES ( 'Sales Fact Table'[purchase_Date] ), [purchase_Date], , DESC, DENSE ) ) RETURN DATEDIFF ( MINX ( FILTER ( temp, [RANK] = 3 ), [purchase_Date] ), MINX ( FILTER ( temp, [RANK] = 2 ), [purchase_Date] ), DAY )
Hi Zubair,
This works great. Thanks! Do you know how I then average all of the rows in the measure column?
E.g.
Customer 1: 3 Days
Customer 2: 8 Days
Customer 3: 12 Days
Or will I have to create a calculated column?
Try this MEASURE to do the average
Measure = AVERAGEX ( ALLSELECTED ( 'Sales Fact Table'[Customer] ), [Days between second order and third order] )
This is working great and I have marked these as solutions.
If I wanted to modify this formula to extract the customer's second purchase date i.e. Joe's second order occurred on 01/07/2018 in a new column, how would I adjust the following formula you provided:
Days between second order and third order = VAR temp = ADDCOLUMNS ( VALUES ( 'Sales Fact Table'[purchase_Date] ), "RANK", RANKX ( VALUES ( 'Sales Fact Table'[purchase_Date] ), [purchase_Date], , DESC, DENSE ) ) RETURN DATEDIFF ( MINX ( FILTER ( temp, [RANK] = 3 ), [purchase_Date] ), MINX ( FILTER ( temp, [RANK] = 2 ), [purchase_Date] ), DAY )
So basically, if the rank of a customer's order = 2 (second order) then return this second order date in a new column.
I have tried many formulas but cannot get it to work
You could try this one
Date of second order = VAR temp = ADDCOLUMNS ( VALUES ( 'Sales Fact Table'[purchase_Date] ), "RANK", RANKX ( VALUES ( 'Sales Fact Table'[purchase_Date] ), [purchase_Date], , ASC, DENSE ) ) RETURN MINX ( FILTER ( temp, [RANK] = 2 ), [purchase_Date] )
Hey @Zubair_Muhammad
Thanks for this suggestion. Unfortunately this does not seem to be working. It returns the same value for each customer. 24/07/2013.
It would seem perhaps that it is returning the first second order date that existed across all customers and assigning it to each customer rather than returning a unique value for each customer
Hi,
Share some data and show the expected result.
Hi @Ashish_Mathur, thanks for your reply!
Data Is as Follows:
purchase_date | product_id | order_id | customer_id |
24/05/2014 | 100 | 6970030474 | 6911468682 |
24/05/2014 | 200 | 6970029258 | 6911468682 |
24/05/2014 | 1111 | 6970028426 | 6911468682 |
24/05/2014 | 800 | 6970030090 | 6911468682 |
24/05/2014 | 100 | 6970029578 | 6911468682 |
24/05/2014 | 2000 | 6970028810 | 6911468682 |
27/05/2014 | 1100 | 6970098378 | 6911468682 |
1/06/2014 | 400 | 6970186122 | 6911468682 |
1/06/2014 | 900 | 6970186506 | 6911468682 |
1/06/2014 | 100 | 6970186954 | 6911468682 |
5/09/2013 | 3000 | 6966721034 | 6904791178 |
5/09/2013 | 2000 | 6966721034 | 6904791178 |
5/09/2013 | 1000 | 6966721034 | 6904791178 |
7/07/2014 | 900 | 6970956234 | 6904791178 |
8/07/2014 | 800 | 6970970506 | 6904791178 |
10/07/2014 | 1100 | 6970999754 | 6904791178 |
11/07/2014 | 400 | 6971025482 | 6904791178 |
29/01/2015 | 300 | 6980551690 | 6904791178 |
29/01/2015 | 100 | 6980551114 | 6904791178 |
29/01/2015 | 200 | 6980551690 | 6904791178 |
29/01/2015 | 200 | 6980551114 | 6904791178 |
29/01/2015 | 200 | 6980551114 | 6904791178 |
29/01/2015 | 50061 | 6980551114 | 6904791178 |
29/01/2015 | 1300 | 6980551690 | 6904791178 |
11/02/2015 | 20000 | 6980978890 | 6904791178 |
11/02/2015 | 6174 | 6980978890 | 6904791178 |
11/02/2015 | 8946 | 6980978890 | 6904791178 |
I have a measure which returns First Order Date:- Date Of First Purchase = FIRSTDATE('Sales Fact Table'[purchase_date])
I have a measure which returns Last Order Date:- Date Of Last Purchase = LASTDATE('Sales Fact Table'[purchase_date])
I would now like in a seperate column to return the customer's second purchase date
So for customer 6904791178 it would return the value 7/07/2014 as this was their date of second purchase
Thanks!
Hi,
Here's the result i got. Download the PBI file from here.
Hi Ashish, thanks for your assistence.
I tried the solution in your PBIX file but it says I have a circular dependancy:
So confused
I really cannot helop now. The maximum i can do is share the file with you which i already have done.
Hi Ashish,
What if we wanted to see it in calculated coloumns
That is a very sold post. Share some data, describe the question and show the expected result.
Absolutely Ashish, appreciate your help!
WHen I use my formula as a MEASURE with sample data...I get correct results
Please see attached file
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |