Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Date | Location | Customer ID | Item | Quantity |
1/1/2014 | 100 | 123 | XYZ | 2 |
... |
I would like to generate a view of "lapsed" and "reactivated" customers per month, i.e.:
Month | Lapsed Customers | Reactivated Customers |
Jan-14 | 59 | 23 |
Feb-14 | 19 | 80 |
... |
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:
and this one which seems close conceptually, but does not have the nuance around lapsed/reactivated in a certain month.
Many thanks!
Solved! Go to Solution.
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
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
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
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
@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
@v-jiascu-msft It's looking good! Let me test out further with the full data set and let you know.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |