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

Hi @v-jiascu-msft - any follow up thoughts on getting the Reactivated Customers to work?

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.

@v-jiascu-msft I think we have cracked this one! Many thanks again for all your help.

 

For the benefit of others reading the thread, am copying in the formulas as they sit in my excel version.

Would you mind linking to the PowerBI file on your end also? I would like to try and port this over to a more lightweight, PowerBI-only version and your template would be a great reference.

 

顧客コード refers to CustomerCode

 

LapsedCustomers:=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
        )
    )



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

Hi @srl01,

 

That's great. Please check this link: https://1drv.ms/u/s!ArTqPk2pu-BkgR7c_gSM0LaZConE.

 

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-msft It's looking good! Let me test out further with the full data set and let you know.

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.