cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## RFM - Recency of latest purchase

Hello everyone,

I'm currently trying to make an analysis that shows what "segmentation" our customers are in regarding the recency of their last purchase.

The goal is to make it possible to know precisely how many customers are in each "segmentation" for a specific year. This specific year will be chosen using a parameter table that has no connection with the other tables and that starts in year 2010.

For the moment, I have this calculated column in the customer table that gives such results : Each segmentation has X number of customers but the analysis is only possible for the latest "purchase date", which means it is not dynamic and only work when the information about the segmentation is given in a "From today" point of view. Hope this explanation is understandable enough.

```recency of last customer's purchase =
IF(
DATEDIFF(
dim_customer[date last purchase(sales)];
MAX(data_sales[purchase_date]);   // I think the solution might be in this area         MONTH                             // => adding a filter that calculates for the selected year
) <= 12
&& ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
"01-12 mois";
IF(
DATEDIFF(
dim_customer[date last purchase(sales)];
MAX(data_sales[purchase_date])
; MONTH
) <= 24
&& ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
"12-24 mois";
IF(
DATEDIFF(
dim_customer[date last purchase(sales)];
MAX(data_sales[purchase_date]);
MONTH
) >= 25
&& ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
"25+ mois"
)
))```

Then, I have a measure that gives the number of distinct customers in each category :

```nb customers =
IF(
[Valeur year last purchase] > YEAR(TODAY());
BLANK();
CALCULATE(
DISTINCTCOUNT(dim_customer[account_id]);
FILTER(dim_customer;
dim_customer[year last purchase] <= [Valeur year last purchase]
)
)
)```

Example of results from 2015. It clearly shows that whenever the date is over 12 months, etc. the "nb customers" is not calculated because DATEDIFF between the last purchase date in the sales table (which is obviously today or yesterday) and the last purchase of each specific customer is longer than 12 months.

Example of results for year 2017

I add the simple distinctcount formula for the total customers :

```Total customers =
CALCULATE(
DISTINCTCOUNT(data_sales[account_id_FK])
)```

R.

1 ACCEPTED SOLUTION
Super User

pls check the dynamic segmentation pattern

I would add a column in the customer table indicating the "AgeOfToday" like this:

`AgeOfToday = YEARFRAC(TODAY(),CALCULATE(MAX(data_sales[purchase_date]), RELATEDTABLE(data_sales)))*12`

then calculate an offset to the selected year like this:

`VAR Offset =    (YEAR ( TODAY () ) - MAX ( PickYear[CalendarYear] ))*12RETURN IF(ISFILTERED(Recency[Recency]),    CALCULATE (        COUNT( DimCustomer[AgeOfToday] ),        FILTER (            VALUES(DimCustomer[AgeOfToday]),            DimCustomer[AgeOfToday]                <= MAX ( Recency[Until] )                    + Offset                && DimCustomer[AgeOfToday]                    > MAX ( Recency[From] )                        + Offset                ||DimCustomer[AgeOfToday]                    > MAX ( Recency[From] ) + Offset && MIN( Recency[Until] ) = BLANK ()        )    ),COUNT(DimCustomer[AgeOfToday]))`

You need a table "Recency" whose first column you drag into the report pane and "From" and "Until" hold the ranges' border like so:

 Recency From Until 0-12 0 12 12-24 12 24 24+ 24

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

5 REPLIES 5
Super User

pls check the dynamic segmentation pattern

I would add a column in the customer table indicating the "AgeOfToday" like this:

`AgeOfToday = YEARFRAC(TODAY(),CALCULATE(MAX(data_sales[purchase_date]), RELATEDTABLE(data_sales)))*12`

then calculate an offset to the selected year like this:

`VAR Offset =    (YEAR ( TODAY () ) - MAX ( PickYear[CalendarYear] ))*12RETURN IF(ISFILTERED(Recency[Recency]),    CALCULATE (        COUNT( DimCustomer[AgeOfToday] ),        FILTER (            VALUES(DimCustomer[AgeOfToday]),            DimCustomer[AgeOfToday]                <= MAX ( Recency[Until] )                    + Offset                && DimCustomer[AgeOfToday]                    > MAX ( Recency[From] )                        + Offset                ||DimCustomer[AgeOfToday]                    > MAX ( Recency[From] ) + Offset && MIN( Recency[Until] ) = BLANK ()        )    ),COUNT(DimCustomer[AgeOfToday]))`

You need a table "Recency" whose first column you drag into the report pane and "From" and "Until" hold the ranges' border like so:

 Recency From Until 0-12 0 12 12-24 12 24 24+ 24

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Anonymous
Not applicable

Sorry for the various answer I made, but I cannot find the right answer after several trials.

I still have question about the "PickYear[CalendarYear]": where does it come from? Is it from the Calendar table or so?

Anonymous
Not applicable

By using the Year of last Purchase value of my parameter table, it works like a charm.

Thanks again for your help @ImkeF!

Anonymous
Not applicable

I've also added a point in the AgeOfToday because the analysis needs to be done at the end of every year.

```AgeOfEndOfYear =
YEARFRAC(
ENDOFYEAR(Calendar[Date]);
CALCULATE(
MAX(data_sales[purchase_date]);
RELATEDTABLE(data_sales)
)
) * 12```
Anonymous
Not applicable

Hello everyone,

I'm currently trying to make an analysis that shows what "segmentation" our customers are in regarding the recency of their last purchase.

The goal is to make it possible to know precisely how many customers are in each "segmentation" for a specific year. This specific year will be chosen using a parameter table that has no connection with the other tables and that starts in year 2010.

For the moment, I have this calculated column in the customer table that gives such results : Each segmentation has X number of customers but the analysis is only possible for the latest "purchase date", which means it is not dynamic and only work when the information about the segmentation is given in a "From today" point of view. Hope this explanation is understandable enough.

```recency of last customer's purchase =
IF(
DATEDIFF(
dim_customer[date last purchase(sales)];
MAX(data_sales[purchase_date]);   // I think the solution might be in this area         MONTH                             // => adding a filter that calculates for the selected year
) <= 12
&& ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
"01-12 mois";
IF(
DATEDIFF(
dim_customer[date last purchase(sales)];
MAX(data_sales[purchase_date])
; MONTH
) <= 24
&& ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
"12-24 mois";
IF(
DATEDIFF(
dim_customer[date last purchase(sales)];
MAX(data_sales[purchase_date]);
MONTH
) >= 25
&& ISBLANK(dim_customer[date last purchase(sales)]) = FALSE() ;
"25+ mois"
)
))```

Then, I have a measure that gives the number of distinct customers in each category :

```nb customers =
IF(
[Valeur year last purchase] > YEAR(TODAY());
BLANK();
CALCULATE(
DISTINCTCOUNT(dim_customer[account_id]);
FILTER(dim_customer;
dim_customer[year last purchase] <= [Valeur year last purchase]
)
)
)```

Example of results from 2015. It clearly shows that whenever the date is over 12 months, etc. the "nb customers" is not calculated because DATEDIFF between the last purchase date in the sales table (which is obviously today or yesterday) and the last purchase of each specific customer is longer than 12 months.

Example of results for year 2017

I add the simple distinctcount formula for the total customers :

```Total customers =
CALCULATE(
DISTINCTCOUNT(data_sales[account_id_FK])
)```

R.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors