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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
srl01
Helper II
Helper II

Calculating lapsed and reactivated customers month by month

Hello community!

 

I have a series of transaction data in a format like below, connected many-to-one via CustomerID to a master table of Customers.

 

DateLocationCustomer IDItemQuantity
1/1/2014100123XYZ2
...    

 

I would like to generate a view of "lapsed" and "reactivated" customers per month, i.e.:

 

MonthLapsed CustomersReactivated Customers
Jan-145923
Feb-141980
...  

 

 

Where:

Lapsed Customers: = count of CustomerIDs which had no sales activity in the 6 months up to and including the specified month (in first column above), AND did have >0 activity in the one month before that (i.e. sales activity in T-6 month).

I.e. Customers counted as lapsed in June would all have purchased in last December, then nothing in Jan/Feb/Mar/Apr/May/Jun.

 

Reactivated Customers:= count of CustomerIDs which had >0 sales activity in the specified month, AND no sales activity in the previous 6 months before that (i.e. between T-6 and T-1 months)

I.e. Customers counted as reactivated in June would all have purchased in June, but nothing in Dec/Jan/Feb/Mar/Apr/May.

 

A customer might lapse and reactivate a number of times through the time period.

 

Would appreicate some guidance in how to build the formulas to calculate these.

Note: I have had a look at this example but it does not seem to reach a solution:

https://community.powerbi.com/t5/Desktop/how-calculate-reactivated-customers/m-p/199356/highlight/tr...

and this one which seems close conceptually, but does not have the nuance around lapsed/reactivated in a certain month.

https://community.powerbi.com/t5/Desktop/Please-help-me-to-think-this-out-How-to-calculate-reactivat...

 

Many thanks!

 

 

 

3 ACCEPTED SOLUTIONS

Hi @srl01,

 

It's a good idea to create a Date table first. Because we are going to use several date functions.

Calendar =
CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) )

Then establish relationship between Date table and your table "Transaction".

Finally, we can get the answers by two measures. The functions start with "test" aim to verify the answer in the attachment.

Lapsed Customers =
VAR Before6 =
    CALCULATETABLE (
        VALUES ( 'Transaction'[Customer ID] ),
        PARALLELPERIOD ( 'Calendar'[Date], -6, MONTH )
    )
VAR During6 =
    CALCULATETABLE (
        VALUES ( 'Transaction'[Customer ID] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'Transaction'[date] ), 0 ),
            -6,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Transaction'[Customer ID] ),
        FILTER (
            ALL ( 'Transaction' ),
            'Transaction'[Customer ID] IN Before6
                && NOT 'Transaction'[Customer ID] IN During6
        )
    )
Reactivated Customers =
VAR CurrentIDs =
    VALUES ( 'Transaction'[Customer ID] )
VAR Last6 =
    CALCULATETABLE (
        VALUES ( 'Transaction'[Customer ID] ),
        DATESINPERIOD (
            'Calendar'[Date],
            STARTOFMONTH ( 'Calendar'[date] ) - 1,
            -6,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Transaction'[Customer ID] ),
        FILTER (
            ALL ( 'Transaction' ),
            'Transaction'[Customer ID] IN CurrentIDs
                && NOT 'Transaction'[Customer ID] IN Last6
        )
    )

PBIX file: https://1drv.ms/u/s!ArTqPk2pu-BkgQCoNU272tlQoQdR

 

Best Regards!

Dale

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

View solution in original post

@srl01,

 

Hi Shane,

 

That's really embarrassing. The cause is 'SalesRawData'[日付] could be empty when there is no data in that month.  I'm afraid Reactivated Customers V2 has the same issue. Please try this one.

Lapsed Customers = 
VAR Before6 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        PARALLELPERIOD ( 'Calendar'[Date], -6, MONTH )
    )
VAR During6 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'Calendar'[Date] ), 0 ),
            -6,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'SalesRawData' ),
            'SalesRawData'[顧客コード] IN Before6
                && NOT 'SalesRawData'[顧客コード] IN During6
        )
    )

 

Best Regards!

Dale

 

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

View solution in original post

Hi @srl01,

 

Try this formula please. The old "Before7" only returns values of one month. 

ReactivatedCustomers =
VAR Before7 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= EOMONTH ( MIN ( 'Calendar'[Date] ), -7 )
        )
    )
VAR During1to7 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'Calendar'[Date] ), -1 ),
            -6,
            MONTH
        )
    )
VAR ThisMonth =
    VALUES ( 'SalesRawData'[顧客コード] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'SalesRawData'[顧客コード] ),
             'SalesRawData'[顧客コード] IN ThisMonth
                && 'SalesRawData'[顧客コード] IN Before7
                && NOT 'SalesRawData'[顧客コード] IN During1to7 
        )
    )

 

Best Regards!

Dale

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

View solution in original post

25 REPLIES 25
v-jiascu-msft
Employee
Employee

Hi @srl01,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
srl01
Helper II
Helper II

I should have noted above - am hoping to define these as measures against each relevant month in the dataset rather than add extra columns, but happy to work with whichever way is simplest.

 

Thank you all!

 

Hi @srl01,

 

It's a good idea to create a Date table first. Because we are going to use several date functions.

Calendar =
CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) )

Then establish relationship between Date table and your table "Transaction".

Finally, we can get the answers by two measures. The functions start with "test" aim to verify the answer in the attachment.

Lapsed Customers =
VAR Before6 =
    CALCULATETABLE (
        VALUES ( 'Transaction'[Customer ID] ),
        PARALLELPERIOD ( 'Calendar'[Date], -6, MONTH )
    )
VAR During6 =
    CALCULATETABLE (
        VALUES ( 'Transaction'[Customer ID] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'Transaction'[date] ), 0 ),
            -6,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Transaction'[Customer ID] ),
        FILTER (
            ALL ( 'Transaction' ),
            'Transaction'[Customer ID] IN Before6
                && NOT 'Transaction'[Customer ID] IN During6
        )
    )
Reactivated Customers =
VAR CurrentIDs =
    VALUES ( 'Transaction'[Customer ID] )
VAR Last6 =
    CALCULATETABLE (
        VALUES ( 'Transaction'[Customer ID] ),
        DATESINPERIOD (
            'Calendar'[Date],
            STARTOFMONTH ( 'Calendar'[date] ) - 1,
            -6,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Transaction'[Customer ID] ),
        FILTER (
            ALL ( 'Transaction' ),
            'Transaction'[Customer ID] IN CurrentIDs
                && NOT 'Transaction'[Customer ID] IN Last6
        )
    )

PBIX file: https://1drv.ms/u/s!ArTqPk2pu-BkgQCoNU272tlQoQdR

 

Best Regards!

Dale

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

Hello!

I want to calculate lapsed volumes for members who appear in the previous month but not in the current month. I have altered your code to replicate that but I am getting 0 for each month. I wonder if you could help.

Lapsed Volume = 
VAR thismonth =
    CALCULATETABLE (
        VALUES ( Credentials[Qual Ref No_] ),
        DATESINPERIOD (
            'Reporting Date'[Reporting Date].[Date],
            EOMONTH ( MIN ( Credentials[Reporting Date] ), 0 ),
            -1,
            MONTH
        )
    )
VAR last2months =
    CALCULATETABLE (
        VALUES ( Credentials[Qual Ref No_] ),
        DATESINPERIOD (
            'Reporting Date'[Reporting Date].[Date],
            EOMONTH ( MIN ( Credentials[Reporting Date] ), 0 ),
            -2,
            MONTH
        )
    )
VAR lapsers = CALCULATETABLE (
        VALUES ( Credentials[Qual Ref No_] ),
        FILTER (
            ALL ( Credentials ),
            Credentials[Qual Ref No_] IN last2months
                && NOT Credentials[Qual Ref No_] IN thismonth
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Credentials[Qual Ref No_] ),
        FILTER (
            ALL ( Credentials ),
            Credentials[Qual Ref No_] IN lapsers
        
        )
    )

 Many thanks,

James

How would you amend this for a 30 day period instead of 6 month? I am trying to figure it out, but am stuck

Hello @v-jiascu-msft and thank you for the detailed response!

Have been offline due to the birth of a child but now getting back into it.

Let me try this out and let you know.

 

Many thanks!

Hi @srl01,

 

Congratulations! Take your time.

 

Best Regards!

Dale

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

@v-jiascu-msftJust one follow up query - would any modification be required to use this code in Powerpivot within Excel? 

Hi @srl01,

 

I think it should work. Why not Power BI?

 

Best Regards!

Dale

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

Hi @v-jiascu-msft reason is end users don't have it so need to package in Excel. But I think I'm getting there! Will report in when set up.

Ok @v-jiascu-msft I think I have it working!

 

And using your examples, I have tried to add in a further Measure called Reactivated Customers, for those who have lapsed and then rejoined. (Specifically, a purchase this month, no purchases in previous 6 months, and a purchase sometime before that)

 

(Sorry for Japanese text due to source data: 顧客コード is CustomerID; 日付 is Date)

Reactivated Customers:=VAR Before7 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        PARALLELPERIOD ( 'Calendar'[Date], -7, MONTH )
    )
VAR During1to7 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'SalesRawData'[日付] ), -1),
            -6,
            MONTH
        )
    )
VAR DuringThisMonth =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'SalesRawData'[日付] ), 0 ),
            -1,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'SalesRawData' ),
            'SalesRawData'[顧客コード] IN DuringThisMonth
                && NOT 'SalesRawData'[顧客コード] IN During1to7
                &&  'SalesRawData'[顧客コード] IN Before7
        )
    )

However I'm not getting the results I expect - ReactivatedCustomers just becomes the value of UniqueCustomers from 7 months previous. Is there something wrong with how I am combining the three conditions with && operators? 

 

Capture.JPG

 

 

 

Hi @srl01,

 

Try this formula please. Please test it before using.

Reactivated Customers V2 =
VAR Before7 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= EOMONTH ( MIN ( 'SalesRawData'[日付] ), -7 )
        )
    )
VAR During1to7 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'SalesRawData'[日付] ), -1 ),
            -6,
            MONTH
        )
    )
VAR DuringThisMonth =
    VALUES ( 'SalesRawData'[顧客コード] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'SalesRawData'[顧客コード] ),
            'SalesRawData'[顧客コード] IN DuringThisMonth
                && NOT 'SalesRawData'[顧客コード] IN During1to7
                && 'SalesRawData'[顧客コード] IN Before7
        )
    )

Best Regards!

Dale

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

Thankyou @v-jiascu-msft! Will try now. Why is the approach to Before7 (using FILTER) different to that of Before6 in the previous example (using PARALLELPERIOD) for Lapsed customers?

Hi @srl01,

 

You can make some changes. I guess Before 7 could be all the months before 7. The PARALLELPERIOD means as its name. One month to another month. I will reply your latest post tomorrow. Good night.

 

Best Regards!

Dale

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

Following up @v-jiascu-msft - there seems to be an issue with the Lapsed (and Reactivated) logic. I have a customer X with the following purchase history (Transactions in each month)

 

9/30/20141
11/30/20142
12/31/20141
1/31/20151
4/30/20151
5/31/20152
6/30/20152
7/31/20151
9/30/20151
1/31/20161
2/29/20161
6/30/20161
7/31/20161
8/31/20161
11/30/20161
2/28/20171
4/30/20171
7/31/20171

 

So no gaps of longer than 6 months - this customer never lapsed.

However in the outputs filtering for only this customer, it is showing up as lapsing and reactivating over time:

 

EndOfMonthTransactionsUniqueCustomersNewCustomersReturning CustomersLapsedCustomersReactivatedCustomers
9/30/2014111   
11/30/201421 1  
12/31/201411 1  
1/31/201511 1  
3/31/2015    1 
4/30/201511 1 1
5/31/201521 1 1
6/30/201521 1 1
7/31/201511 1 1
9/30/201511 1 1
10/31/2015    1 
11/30/2015    1 
12/31/2015    1 
1/31/201611 1 1
2/29/201611 1 1
3/31/2016    1 
6/30/201611 1 1
7/31/201611 1 1
8/31/201611 1 1
11/30/201611 1 1
12/31/2016    1 
1/31/2017    1 
2/28/201711 1 1
4/30/201711 1 1
5/31/2017    1 
7/31/201711 1 1

 

Can you think of why this might be happening?

 

Here is the code again just to be sure:

 

LapsedCustomers:=VAR Before6 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        PARALLELPERIOD ( 'Calendar'[Date], -6, MONTH )
    )
VAR During6 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'SalesRawData'[日付] ), 0 ),
            -6,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'SalesRawData' ),
            'SalesRawData'[顧客コード] IN Before6
                && NOT 'SalesRawData'[顧客コード] IN During6
        )
    )
ReactivatedCustomers:=VAR Before7 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= EOMONTH ( MIN ( 'SalesRawData'[日付] ), -7 )
        )
    )
VAR During1to7 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'SalesRawData'[日付] ), -1 ),
            -6,
            MONTH
        )
    )
VAR DuringThisMonth =
    VALUES ( 'SalesRawData'[顧客コード] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'SalesRawData'[顧客コード] ),
            'SalesRawData'[顧客コード] IN DuringThisMonth
                && NOT 'SalesRawData'[顧客コード] IN During1to7
                && 'SalesRawData'[顧客コード] IN Before7
        )
    )

 

 

 

Hi @srl01,

 

This is exactly what I have struggled with almost the whole day yesterday. I am still working on it. According to my test and analysis, this formula can return right result. Only a small change in the blue part. Please have a try.

 

LapsedCustomers 2 = VAR Before6 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        PARALLELPERIOD ( 'Calendar'[Date], -6, MONTH )
    )
VAR During6 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'SalesRawData'[日付] ), 0 ),
            -6,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'SalesRawData'[顧客コード] ),
            'SalesRawData'[顧客コード] IN Before6
                && NOT 'SalesRawData'[顧客コード] IN During6
        )
    )

Calculating lapsed and reactivated customers month by month.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

@v-jiascu-msftSuperb - thank you so much!

Now i need to fix the Reactivated calcs also but will try and push that myself first and report in.

 

@v-jiascu-msftThat last change appears to have caused the lapsed customer calculation to stop working - only get a value in the GrandTotal row but nothing in the individual months.

 

EndOfMonthTransactionsNewCustomersReturning CustomersLapsedCustomers
3/31/2015737585  
4/30/2015101970473 
5/31/2015947551174 
6/30/2015832406195 
7/31/2015864425232 
8/31/20151004461282 
9/30/2015853400264 
10/31/20151023446303 
11/30/20151004396330 
12/31/20151417566432 
1/31/20161099462347 
2/29/20161024365363 
3/31/20161300521432 
4/30/2016419147183 
5/31/2016827252384 
6/30/20161050370404 
7/31/20161189387475 
8/31/20161176357510 
9/30/20161065313465 
10/31/20161127298523 
11/30/20161124324483 
12/31/20161590448743 
1/31/20171268374590 
2/28/2017989305525 
3/31/20171357389635 
4/30/20171034278554 
5/31/20171193316665 
6/30/20171103240632 
7/31/20171125288612 
8/31/20171100267609 
Grand Total3185911641 

9278

 

Are you getting different outcomes in PowerBI?

 

Shane

@srl01,

 

Hi Shane,

 

That's really embarrassing. The cause is 'SalesRawData'[日付] could be empty when there is no data in that month.  I'm afraid Reactivated Customers V2 has the same issue. Please try this one.

Lapsed Customers = 
VAR Before6 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        PARALLELPERIOD ( 'Calendar'[Date], -6, MONTH )
    )
VAR During6 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'Calendar'[Date] ), 0 ),
            -6,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'SalesRawData' ),
            'SalesRawData'[顧客コード] IN Before6
                && NOT 'SalesRawData'[顧客コード] IN During6
        )
    )

 

Best Regards!

Dale

 

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

@v-jiascu-msftThank you - that seemed to hit the spot!

 

Am trying to correct the Reactivated formula but not getting the right results.

An example for one customer - should show up as Reactivated in 1/31/2016:

 

EndOfMonthTransactionsNewCustomersReturning CustomersLapsedCustomersReactivatedCustomers
10/31/201411   
1/31/20151 1  
5/31/20151 1 1
11/30/2015   1 
1/31/20161 1  
7/31/2016   1 

Current setup is below (I have included the date adjustments to reference Calendar[Date].

Can you see where the issueis ?

 

 

ReactivatedCustomers:=VAR Before7 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        PARALLELPERIOD ( 'Calendar'[Date], -7, MONTH )
    )
VAR During1to7 =
    CALCULATETABLE (
        VALUES ( 'SalesRawData'[顧客コード] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( 'Calendar'[Date] ), -1 ),
            -6,
            MONTH
        )
    )
VAR DuringThisMonth =
    VALUES ( 'SalesRawData'[顧客コード] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'SalesRawData'[顧客コード] ),
        FILTER (
            ALL ( 'SalesRawData'[顧客コード] ),
            'SalesRawData'[顧客コード] IN DuringThisMonth
                && NOT 'SalesRawData'[顧客コード] IN During1to7
                && 'SalesRawData'[顧客コード] IN Before7
        )
    )

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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