cancel
Showing results for
Did you mean:
Highlighted
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.
Highlighted
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

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

## Re: Calculating lapsed and reactivated customers month by month

Hi @srl01,

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

Announcements

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

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

#### Community Blog

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

#### Community Summit Australia – Join Online!

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

Top Solution Authors
Top Kudoed Authors