Days difference between when the first items and second item was purchased

[WhyIsThis]

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...

[Ashish_Mathur · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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?

[WhyIsThis]

@Ashish_Mathur  - please see attached data link below with the expected output results. Thanks

Link to the Sample_Data  

[Ashish_Mathur · Accepted solution]

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.

[WhyIsThis]

@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

[punitkhatri]

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 )

RETURN

    IF(NextDate <> BLANK(), DateDiffference, 0)

This is the output I gotThis is the dataset that I've used.

[Ashish_Mathur · Accepted solution]

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?

[WhyIsThis]

@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

[Ashish_Mathur]

Please see my post dated June 29.  The result there matches with your expected result.

[WhyIsThis]

@Ashish_Mathur - Thanks! I have a feeling that the DAX you provided will work, its returns the exact day's diff in the dataset i provided, but it's returning something different in the real dataset I am working on. I need to do some updates on the report dates. I will let you know the outcome. Thanks again

[WhyIsThis · Accepted solution]

@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 🙌

[Ashish_Mathur]

You are welcome.

[WhyIsThis]

@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.

[Ahmedx]

pls try this

difference between first and secon purchase.pbix

[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.

[Ritaf1983]

Hi @WhyIsThis 
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

__result

Link to a sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly