New customer returning after 90 days

[Anonymous]

Hi, 

I have a table like this : 

Sales order

Email

transaction ID

I would like to create a measure that calculate 90 days retention. 

90 days retention : New customers that came back for a second order within the first 90 days. 

The table would look like this : 

Month first order	1	2	3	4	5	6
Jan	24%	20%	10%
Feb
March

I am already using a measure to calculate 3 month rolling but it's takes the month. I would like to do per day to be more precise thus 90 days since date first order.

Can anyone help me out with this ? i am kind of lost ..

Thanks a lot ! 

[v-yangliu-msft]

Hi  @Anonymous  ,

According to your description, I create some data:

Here are the steps you can follow：

1. Create measure.

90 =
var _minday=MINX(FILTER(ALL('Table (2)'),[Sales order]=MAX([Sales order])),[date])
return
COUNTX(FILTER(ALL('Table (2)'),[date]>_minday&&[date]<=_minday+90&&[Sales order]=MAX([Sales order])),[Sales order])

120 =
var _minday=MINX(FILTER(ALL('Table (2)'),[Sales order]=MAX([Sales order])),[date])
return
COUNTX(FILTER(ALL('Table (2)'),[date]>_minday&&[date]<=_minday+120&&[Sales order]=MAX([Sales order])),[Sales order])

365 =
var _minday=MINX(FILTER(ALL('Table (2)'),[Sales order]=MAX([Sales order])),[date])
return
COUNTX(FILTER(ALL('Table (2)'),[date]>_minday&&[date]<=_minday+365&&[Sales order]=MAX([Sales order])),[Sales order])

2. Create calculated table.

Table2 =
SUMMARIZE(
    FILTER('Table','Table'[Flag]=0),'Table'[Category ],'Table'[date1],"duration",SUM('Table'[Datediff]))

3. Result.

You can downloaded PBIX file from here.

Does this meet your expected results?

Best Regards,

Liu Yang

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

[v-yangliu-msft]

Hi  @Anonymous ,

According to your description, I create this data:

Here are the steps you can follow：

1. Create calculated column.

Month = MONTH('Table'[date])

2. Create measure.

Measure = 
var _1=CALCULATE(COUNT('Table'[transaction ID]),FILTER(ALL('Table'),
'Table'[Month]=MAX('Table'[Month])-3&&
'Table'[Sales order]=MAX('Table'[Sales order])&&
'Table'[transaction ID] in SELECTCOLUMNS('Table',"1",'Table'[transaction ID] )))
var _2=COUNT('Table'[transaction ID])
return 
DIVIDE(_1,_2)

3. Result:

You can downloaded PBIX file from here.

If the result does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

Best Regards,

Liu Yang

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

[Anonymous]

Thank you for your answer. i am looking for a way to count 90 120 and 365 days and not per month. I am already doing that. The idea is to count from a day a customer had he first order and not the month.

Best regards 

Ilhame

[amitchandak]

@Anonymous , I have something similar in the video link and blog link. You need to create a column rank , day since last purchase and add filters

these are new column

rank = rankx(filter(Table, [customer] = earlier([customer])), [purchase date],,asc,dense)

diff with last purcahse = datediff(maxx(filter(Table, [customer] = earlier([customer]) && [purchase date] <earlier([purchase date])), [purchase date]),[purchase date],day)

rank <=2 and  diff with last purchase  <=90

you need a couple more column-like first purchase month etc. refer to my video and blog

PowerBI Customer Retention Part 3: Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Analysis/ba-p/1393410

video

https://www.youtube.com/watch?v=Q1vPWmfI25o

[Anonymous]

Thank you very much i will try your way and see if i get the results 

Much appreciation 

Best regards 

Ilhame