Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
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]
	)
    )
)

 

image.png

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.

 

image.png 

 

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])
)

Thanks in advance for your help that will be much appreciated.

 

R.

1 ACCEPTED SOLUTION
ImkeF
Super User
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] ))*12
RETURN 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:

 

RecencyFromUntil
0-12012
12-241224
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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
ImkeF
Super User
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] ))*12
RETURN 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:

 

RecencyFromUntil
0-12012
12-241224
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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeFThanks for your answer and the blog post you mentioned.

 
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?

 

Thanks for your help!

 

 

 

 

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]
	)
    )
)

image.png

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.

 

 

image.png

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])
)

Thanks in advance for your help that will be much appreciated.

 

R.

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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