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 average delays of purchase

this is for one customer

if i change for another customer he might gave Days since Last purchased (10+ 20+ 30+50+60) = 170

then divide it with Invoice Distinctcount (Summary) 5

he might have 5 indivisual invoice date. so 170/=5 = 34 is his average delays of purchase

I tried following,

Average Days Since Last Purchase = DIVIDE([Days Since last Purchase], [Invoice DISTINCTCOUNT (Summary)], 0)

[Days Since last Purchase] is a measure

[Invoice DISTINCTCOUNT (Summary)] is a measure

when i use Average Days Since Last Purchase measure i got result -46.33, mean is divide -139 with 3 which is not correct at all.

and -139 is the lowest value of Days Since last Purchase column

please help me to find average days

thanks

Need Help

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.
sample-summarize.pbix43 KB

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.

sample-summarize.pbix45 KB

7 Replies

abc_777
Solution Specialist
3 years ago
hi

here is the measure

Days Since last Purchase = DATEDIFF( TODAY(),'1. Measure Table- Retail'[Day of Last Purchase],DAY)

and

Day of Last Purchase = LASTDATE('bm_retail_t sale'[INVOICE_DT])

i want to make average of Days Since last Purchase. please chorrect the measure for average

Average Days Since Last Purchase = DIVIDE([Days Since last Purchase], [Invoice DISTINCTCOUNT (Summary)], 0)

thanks
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.
sample-summarize.pbix43 KB
- abc_777
  Solution Specialist
  3 years ago
  perfect man,
  
  thats the thing i wanted. really great.
  
  thanks for your help
- 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
    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.
    sample-summarize.pbix45 KB

Forum Discussion

hi