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 ...
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 🙌
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
__result
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly