topic Re: Percentage of customers who purchased again after a certain period of time in DAX Commands and Tips

Percentage of customers who purchased again after a certain period of time

yoyowes — Fri, 09 Dec 2022 19:03:13 GMT

Hello,

I am trying to calculate the percentage of customers who purchased again from 300 to 400 days after their first order
My data model looks like this:

So if customers first purchased at january 2021, i need to find their future orders from november 2021 to february 2022, count the number of clients, and divide it by the nubmer of clients in january 2021

I want to have this kind of table and have a product slicer:

I know how to calculate this if i have second purchase date at the same month future year,

but i can`t figure out what to do with custom period

Re: Percentage of customers who purchased again after a certain period of time

FreemanZ — Sat, 10 Dec 2022 02:18:03 GMT

hi yoyowes

Not so sure about your expectation. You may try to create a measure with this:

ReturnPct = VAR CurrentMonth = MAX(TableName[Month-Year]) VAR CurrentClient = COUNTROWS( FILTER( TableName, TableName[Month-Year] = CurrentMonth ) ) VAR StartDate = MINX( FILTER(DateTable, DateTable[Month]=CurrentMonth), 'DateTable'[Date] ) VAR HistoryClient = COUNTROWS( FILTER( ALL(TableName), TableName[OrderDate]>=StartDate-400&&TableName[OrderDate]<=StartDate-300 ) ) RETURN FORMAT(DIVIDE(CurrentClient, HistoryClient),"0.0%")

i tried and it worked like this:

Re: Percentage of customers who purchased again after a certain period of time

v-yueyunzh-msft — Mon, 12 Dec 2022 02:28:06 GMT

Hi , yoyowes

According to your description, you want to "calculate the percentage of customers who purchased again from 300 to 400 days after their first order". Right?

Here are the steps you can refer to :
(1)This is my test data :

(2)We can create a calendar table and we do not need to create relationship between two tables:

Table = CALENDAR(FIRSTDATE('Order'[Order date]), LASTDATE( 'Order'[Order date]))

(3)Then we can create a measure :

Measure = var _cur_date = MIN('Table'[Date]) var _cur_customer =DISTINCT(SELECTCOLUMNS( FILTER('Order' , YEAR('Order'[Order date]) = YEAR(_cur_date) && MONTH('Order'[Order date]) = MONTH(_cur_date)), "Customer" , [Customer])) var _after_first_date =IF(_cur_date<> BLANK(), EOMONTH(_cur_date , 9)+1) var _after_last_date =IF(_cur_date<> BLANK(), EOMONTH(_cur_date , 12)+1) var _after_customer =SELECTCOLUMNS( FILTER('Order' , 'Order'[Order date]>= _after_first_date && 'Order'[Order date] <= _after_last_date) , "Customer" , [Customer]) var _same_customer =COUNTROWS( DISTINCT(INTERSECT(_after_customer,_cur_customer))) var _cur_total = COUNTROWS(_cur_customer) return DIVIDE(_same_customer,_cur_total)

(4)Then we can put the fields we need on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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