Active / LeavingCustomers Measures

[Anonymous]

Hello, 

I am creating column chart by years/yearmonths with customers segmented by their activity:

Active Customer:  customer has status 'active'  up to 15 month after his order.  Measure is OK

Leaving Customer: this 'status' customer acquires when it is exactly 16 months (not 17 and more) after his last order and he is not in 'active' status. Need help with this

Active Customer is calculated below. However i need help with calculation of Leaving Customer

For calculating active customers in selected period (15 months after each order) I was following this guide:

active rows

Source table structure:

CustomerEmail	PurchaseDate	Untill 15M (calculated column)
customer1	2018-01-01	2019-04-01
customer1	2018-06-01	2019-09-01
customer2	2016-01-01	2017-04-01
customer2	2020-01-01	2021-04-01

ActiveCustomer_tmp_measure_TotalRows:
=DISTINCTCOUNT(ActiveCustomer[CustomerEmail])

ActiveCustomers Last15M:=
CALCULATE([ActiveCustomer_tmp_measure_TotalRows];
	FILTER(ActiveCustomer;
		(ActiveCustomer[PurchaseDate]<=LASTDATE('Calendar'[Date])
		&&
		ActiveCustomer[Untill 15M]>=FIRSTDATE('Calendar'[Date]))))

Thanks!

[Greg_Deckler · Accepted solution]

@Anonymous - Try this out:

Leaving Customers =
  VAR __Today = TODAY()
  VAR __Table = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        SUMMARIZE('Table',[CustomerEmail],"End15",MAX([Until 15M])),
        "16",EOMONTH([End15,1])
      ),
      "Leaving",IF(__Today > [End15] && __Today <=[16],1,0)
    )
RETURN
  COUNTROWS(FILTER(__Table,[Leaving]=1))

[v-shex-msft · Accepted solution]

Hi @Anonymous,

I'd like to suggest you take a look at following link about hr employee analytic if it meet your requirement:

HR Data - Slowly Changing Dimensions 

Regards,

Xiaoxin Sheng

[Greg_Deckler]

@Anonymous - Not exactly following this, so when would those customers be considered inactive?

[Anonymous]

hello,

@Greg_Deckler 

active- 0 - 15

leaving - 16

inactive - 17+

inactive is easy to calculate as 'total cumulative' - 'active 17'

problem is just 16 months - customer gets this status when it is 16 months after his order and there were no other orders in this period

@amitchandak 

it doesnt' count customers but sums revenue and the second measure not works properly

[Greg_Deckler · Accepted solution]

@Anonymous - Try this out:

Leaving Customers =
  VAR __Today = TODAY()
  VAR __Table = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        SUMMARIZE('Table',[CustomerEmail],"End15",MAX([Until 15M])),
        "16",EOMONTH([End15,1])
      ),
      "Leaving",IF(__Today > [End15] && __Today <=[16],1,0)
    )
RETURN
  COUNTROWS(FILTER(__Table,[Leaving]=1))

[amitchandak]

@Anonymous , not very clear. if customer is inactive if he not coming for last 16 month

example - Measures

purchased in last 16 month

Rolling 16 Sales = CALCULATE(sum(Retail[Qty]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-3,MONTH))

not purchsed in last 16 month
Rolling 16 Not Sold = if(CALCULATE(sum(Retail[Qty]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-3,MONTH)) +0>0,blank(),1)