cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

Re: Calculating lapsed and reactivated customers month by month

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

Highlighted
Helper II
Helper II

Re: Calculating lapsed and reactivated customers month by month

@v-jiascu-msft It's looking good! Let me test out further with the full data set and let you know.

Highlighted
Helper II
Helper II

Re: Calculating lapsed and reactivated customers month by month

@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 
        )
    )
Highlighted
Microsoft
Microsoft

Re: Calculating lapsed and reactivated customers month by month

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.
Highlighted
Frequent Visitor

Re: Calculating lapsed and reactivated customers month by month

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors