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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Ashish_Mathur - Here is the attached data and expected result. Thanks
 

partner_keypartner_namesalesperson_keysales_datesalescurrency_keyvendor_keyvendor_namecustomer_keyproduct_keyquantityexpected_results_date_difNote:
206448htl30022/25/20230.06513564341cubic358989695650330first purchase
194440abc30592/19/202324.063564341microsoft352234725451030first purchase
194440abc30323/15/202303561236powerbi3557263654510252828 days diffrence from first purchase
189477bda31223/1/202388.223564444jira3583325854510110first purchase
189477bda31224/10/202348.123566666spring355616945451064141 days diffrence from first purchase
210782ccc31225/25/2023208.253565555aquafina355109205783950first purchase
210782ccc34826/20/20233.543567777hawatour352140795848412626 days diffrence from first purchase
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    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
      Not applicable
      3 years ago

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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.