Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Active / LeavingCustomers Measures

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:

CustomerEmailPurchaseDateUntill 15M (calculated column)
customer12018-01-012019-04-01
customer12018-06-012019-09-01
customer22016-01-012017-04-01
customer22020-01-012021-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!

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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)

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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))
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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