Forum Discussion
Days difference between when the first items and second item was purchased
Hey PBI Experts - I need help calculating the day difference between when customers purchase the first and second items.
e.g. - when a customer purchases a laptop for the first time and later comes back after some days or months to buy another item, am i trying to calculate the day difference between the first and second transaction dates? Any idea, please...
Hi,
This calculated column formula works
Column = if(ISBLANK(CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date])))),0,Data[sales_date]-CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date]))))Hope this helps.
Hi,
I am not sure how you arrived at those number - mine are different. I just copied the same calculated column formula which i shared with you ealrier and it worked fine. If you wish to include both days, just add 1
Column = if(ISBLANK(CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date])))),0,Data[sales_date]-CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date])))+1)Have you even tried my formula?
Ashish_Mathur Thanks for the Dax Expression. We had to create a column in the warehouse to bring in another updated_date, and i used the expression you provided in PBI and tweaked it to include the new column, and it works! Thanks 🙌
15 Replies
- Ritaf1983
Super User
Hi Anonymous
Try this measure :Result_day =var __atleast2day = COUNTROWS('Table')var __dynam = TOPN(2, CALCULATETABLE('Table'), 'Table'[date], ASC)var __first = maxx(__dynam, 'Table'[date])var __second = minx(__dynam, 'Table'[date])var __result = if(__atleast2day >1, __first - __second, 0)return__resultIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
- Ashish_Mathur
Hi,
Write this calculated column formula
Days lapsed since previous purchase = 1*(Data[Date]-calculate(max(data[date]),filter(Data,Data[Customer id]=earlier(Data[Customer id])&&Data[Date]<earlier(Data[Date]))))
Hope this helps.
- Ahmedx
Ashish_Mathur Ahmedx Ritaf1983 Thank you all for your professional input. Unfortunately, i tried all the suggested solutions and am still not getting the desired results. To give a live scenario of how my data looks. I have a partner buying from different vendors, i want to calculate the AVERAGE day's difference between the first and second or more purchases by unique partners from these different vendors. Any idea, please...
- Ashish_Mathur
Hi,
Share some data to work with and show the expected result very clearly.
Ashish_Mathur - Here is the attached data and expected result. Thanks
partner_key partner_name salesperson_key sales_date sales currency_key vendor_key vendor_name customer_key product_key quantity expected_results_date_dif Note: 206448 htl 3002 2/25/2023 0.0651 356 4341 cubic 35898969 56503 3 0 first purchase 194440 abc 3059 2/19/2023 24.06 356 4341 microsoft 35223472 54510 3 0 first purchase 194440 abc 3032 3/15/2023 0 356 1236 powerbi 35572636 54510 25 28 28 days diffrence from first purchase 189477 bda 3122 3/1/2023 88.22 356 4444 jira 35833258 54510 11 0 first purchase 189477 bda 3122 4/10/2023 48.12 356 6666 spring 35561694 54510 6 41 41 days diffrence from first purchase 210782 ccc 3122 5/25/2023 208.25 356 5555 aquafina 35510920 57839 5 0 first purchase 210782 ccc 3482 6/20/2023 3.54 356 7777 hawatour 35214079 58484 1 26 26 days diffrence from first purchase - Ashish_Mathur
Hi,
I am not sure how you arrived at those number - mine are different. I just copied the same calculated column formula which i shared with you ealrier and it worked fine. If you wish to include both days, just add 1
Column = if(ISBLANK(CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date])))),0,Data[sales_date]-CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date])))+1)Have you even tried my formula?
Ashish_Mathur - Sorry, and thank you for your time. The logic you gave was helpful but still did not return the desired output. Here is how i have come about the number: The numbers in the expected result column are days dif, the days dif between when a partner makes their first purchase from one vendor and the second purchase from another vendor. That's what I am trying to achieve. I am trying to see the day's diff between when a partner makes a first and second purchase from a different vendor. Thanks
You can try this measure for your desired output,
AverageVisits =VAR CurrentDate =FIRSTDATE ( 'Table'[SaleDate] )VAR NextDate =CALCULATE (MIN ( 'Table'[SaleDate] ),FILTER (ALLEXCEPT ( 'Table', 'Table'[Id] ),'Table'[SaleDate] > MIN ( 'Table'[SaleDate] )))VAR DateDiffference =DATEDIFF ( CurrentDate, NextDate, DAY )RETURNIF(NextDate <> BLANK(), DateDiffference, 0)This is the output I gotThis is the dataset that I've used.
Ashish_Mathur - please see attached data link below with the expected output results. Thanks
Link to the Sample_Data- Ashish_Mathur
Hi,
This calculated column formula works
Column = if(ISBLANK(CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date])))),0,Data[sales_date]-CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date]))))Hope this helps.
