Forum Discussion

abc_777's avatar
abc_777
Solution Specialist
3 years ago
Solved

hi

Hello,   I want to add Days since Last purchased (189 + 155 + 139) = 483 then divide it with Invoice Distinctcount (Summary) 3   as i have 3 indivisual invoice date. so 483/3 = 161 is his ave...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi abc_777 ,

     

    Based on your description, I have created a simple sample:

    Please try:

    Days Since last Purchase = SUMX(SUMMARIZE('bm_retail_t sale','bm_retail_t sale'[INVOICE_DT]),DATEDIFF( TODAY(),'1  Measure Table- Retail'[Day of Last Purchase],DAY))

    Final output:

    Best Regards,

    Jianbo Li

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

  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi abc_777 ,

     

    Please try:

    Days Since last Purchase = 
SUMX(SUMMARIZE('bm_retail_t sale','bm_retail_t sale'[Customer],'bm_retail_t sale'[INVOICE_DT]),DATEDIFF(MAXX(FILTER(ALL('bm_retail_t sale'),[Customer]=EARLIER('bm_retail_t sale'[Customer])&&[INVOICE_DT]<EARLIER('bm_retail_t sale'[INVOICE_DT])),[INVOICE_DT]),'1  Measure Table- Retail'[Day of Last Purchase],DAY))

    Final output:

    Best Regards,

    Jianbo Li

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

v-jianboli-msft's avatar
v-jianboli-msft
Community Support
3 years ago

Hi abc_777 ,

 

Based on your description, I have created a simple sample:

Please try:

Days Since last Purchase = SUMX(SUMMARIZE('bm_retail_t sale','bm_retail_t sale'[INVOICE_DT]),DATEDIFF( TODAY(),'1  Measure Table- Retail'[Day of Last Purchase],DAY))

Final output:

Best Regards,

Jianbo Li

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

abc_777's avatar
abc_777
Solution Specialist
3 years ago

hi v-jianboli-msft ,

 

I made a little mistake to describe you.

solution you gave is perfect but as a logic we have to calculate date not from today but from each difference so we can find the customer buying frequency

 

example, customer bought products as per invoice_date

 

1 january 2023 (01-01-2023)

4 april 2023 (04/04/2023)

20 june 2023 (20/06/2023)

15 july 2023 (15/07/2023) >>>> is his last purchase date. when he again purchase next day some time that would be his last purchase day and 15 july 2023 would be second last purchase day

 

so to find out his buying frequency we need to deduct date to date,

15 july 2023 (15/07/2023) - 20 june 2023 (20/06/2023) = 25 days

20 june 2023 (20/06/2023) - 4 april 2023 (04/04/2023) = 77 days

4 april 2023 (04/04/2023) - 1 january 2023 (01-01-2023) = 93 days

 

then we do average of  (25 days + 77 days + 93 days) / Invoice DISTINCTCOUNT (Summary) = 65 days is his average buying frequency

 

please help me for this calculation

 

thx

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi abc_777 ,

     

    Please try:

    Days Since last Purchase = 
SUMX(SUMMARIZE('bm_retail_t sale','bm_retail_t sale'[Customer],'bm_retail_t sale'[INVOICE_DT]),DATEDIFF(MAXX(FILTER(ALL('bm_retail_t sale'),[Customer]=EARLIER('bm_retail_t sale'[Customer])&&[INVOICE_DT]<EARLIER('bm_retail_t sale'[INVOICE_DT])),[INVOICE_DT]),'1  Measure Table- Retail'[Day of Last Purchase],DAY))

    Final output:

    Best Regards,

    Jianbo Li

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

      • abc_777's avatar
        abc_777
        Solution Specialist
        3 years ago

        v-jianboli-msft 

         

        hello there,

         

        have you got any time to have a look into the isssue for quantity comes from both table to check the heighest and lowest cross saleing product?

        thanks