Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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's avatar
    Ashish_Mathur
    3 years ago

    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?

  • Anonymous's avatar
    Anonymous
    3 years ago

    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 ğŸ™Œ