Need guidance for finding Recency and Frequency

[Anonymous]

Hi I'm a newbie learning how to use power BI . My self study  assignment was to find out both the recency of a customer that made their purchase at the store and also the frequency of customer whom return to purchase in store. Do appreciate that some advance user may guide me through step by step. Thank you .

[Anonymous]

Hi @Anonymous ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Stephen Tao

[Anonymous]

Hi @Anonymous ,

1.Here's my sample data.

2.Create a calculated column to get the distance interval.

Distance interval = DATEDIFF(CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Customer])),CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Customer])),DAY)

3.Create measures.

Number of intervals = CALCULATE(COUNT('Table'[Customer]),FILTER('Table',[Customer]=MAX('Table'[Customer])&&[Distance interval]=MAX('Table'[Distance interval])))

Recency = DIVIDE([Number of intervals], CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Shop])))

Frequency = DIVIDE(CALCULATE(DISTINCTCOUNT('Table'[Customer]),FILTER('Table',[Distance interval]<>0)), CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Shop])))

 

You can check more details from here.

Best Regards,

Stephen Tao

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